Performance issue with indexes in joined tables
am 14.01.2003 00:50:01 von Steve FeveileHow-To-Repeat:
Simply run the query against a database database with minimal data. You
would notice a substantial and and unreasonable delay in the query response
when the two queries are created. I can pass along valid tables for a
database if request.
We have created a web based application that can use both MySQL and MS SQL
databases. Everything seems to be working pretty good, but we have come
across an issue that does not seem to make a lot of sense to us, and we
suspect it may be an oddball bug that would come up only under specific
circumstances.
The problem deals with the performance difference of two queries where when
we run the first query, the result is almost instantaneous where as when we
run virtually the same query against different tables, a substantial slow
down occurs. The difference in speed is basically less than 1 second in the
first case to 65 seconds in the second case. When we run the two queries on
MS SQL, we find no difference, working at about the same response time as
that of the first query. See the two queries at the bottom.
Other background:
1) The application is used fo monitor workload in the medical industry. The
queries are used to determine patient workload (the PSData/PSHeader tables)
and staff workload (the SSData/SSHeader tables) for reporting purposes. The
faster query is the patient workload while the staff workload is far slower.
2) There are very few records in the database. The PSHeader/SSHeader table
each only have a couple of records. The PSData and SSData have about 10
records each. The Code table which is used to join against multiple fields
in the in both the PSHeader and SSHeader tables has about 50 records.
3) When I remove all indexes from the tables in question, the the query in
the slow case is more in line with regards to response with the first query.
4) If I remove only the primary key index from the Code table, the second
query becomes slower (I end the task after 5 minutes).
5) The user uses a UI to pick the criteria for a report. Where ever you see
something like "Code03 > 0", it is basically getting all Codes that have a
set value in the Code03 field. We build up the query using inner joins and
table aliases that can create large queries.
6) I am running 1G pentium with Windows XP, 256 MB ram
We have not yet fine tuned the indexes in our database. There essentially
is only the primary indxes on the tables in question with a few foriegn keys
in place.
The crux of the problem is that we cannot not determine why the second query
is so slow when both queries have a similar structure and look like they
should perform about the same. The fact that the queries run virtually the
same on an MS SQL server indicates to us that the issue is not simply the
heinous query involved. I would think that this is an unusual case since
rare for a query of this design to be used. However, since this appears to
be some indexing problem that might manifest itself in other ways, it seemed
worth bringing to your attention.
If it would be helpful, I could bundle up the 10 tables in question and send
them your way for analysis. You can easily see the slowdown by running the
below queries in the MySQL Control Center Console.
Could you contact me to let me know if this is indeed a bug and if so, if it
is worth pursuing. If there is anything you need from me, please feel free
to contact me.
Thank you.
Steve Feveile
InfoMed Development Corp.
250 338-8900
The queries in question are:
------------------------------------------------------------
SELECT
ProfessionPCAC.ID as ActivityCategoryID,ProfessionPCAC.Name as CategoryName,
ProfessionPCA.ID as ActivityID,ProfessionPCA.Name as ActivityName,
Profession.Description,
Code.Name as InOutStatus ,
StaffCode.Name as StaffName , StaffCode.ID as StaffCodeID,
StaffCode.Description as StaffDescription ,
Code01Code.Name as Code01Name , Code01Code.ID as Code01CodeID,
Code01Code.Description as Code01Description ,
Code02Code.Name as Code02Name , Code02Code.ID as Code02CodeID,
Code02Code.Description as Code02Description ,
Code03Code.Name as Code03Name , Code03Code.ID as Code03CodeID,
Code03Code.Description as Code03Description ,
Code04Code.Name as Code04Name , Code04Code.ID as Code04CodeID,
Code04Code.Description as Code04Description ,
Code05Code.Name as Code05Name , Code05Code.ID as Code05CodeID,
Code05Code.Description as Code05Description ,
Code06Code.Name as Code06Name , Code06Code.ID as Code06CodeID,
Code06Code.Description as Code06Description ,
Code07Code.Name as Code07Name , Code07Code.ID as Code07CodeID,
Code07Code.Description as Code07Description ,
Code08Code.Name as Code08Name , Code08Code.ID as Code08CodeID,
Code08Code.Description as Code08Description ,
Code09Code.Name as Code09Name , Code09Code.ID as Code09CodeID,
Code09Code.Description as Code09Description ,
Code10Code.Name as Code10Name , Code10Code.ID as Code10CodeID,
Code10Code.Description as Code10Description ,
Code11Code.Name as Code11Name , Code11Code.ID as Code11CodeID,
Code11Code.Description as Code11Description
FROM
(((((((((((((((((ProfessionPCAC INNER JOIN Profession ON
ProfessionPCAC.ProfessionID = Profession.ID)
INNER JOIN ProfessionPCA ON ProfessionPCAC.ID =
ProfessionPCA.ActivityCategoryID)
INNER JOIN PSData ON ProfessionPCA.ID = PSData.WorkloadFieldID)
INNER JOIN PSHeader ON PSData.HeaderID = PSHeader.HeaderID)
INNER JOIN Code ON PSHeader.SourceOfRequestID = Code.ID)
INNER JOIN Code StaffCode ON PSHeader.StaffCodeID = StaffCode.ID)
INNER JOIN Code Code01Code ON PSHeader.Code03 = Code01Code.ID)
INNER JOIN Code Code02Code ON PSHeader.Code04 = Code02Code.ID)
INNER JOIN Code Code03Code ON PSHeader.Code05 = Code03Code.ID)
INNER JOIN Code Code04Code ON PSHeader.Code06 = Code04Code.ID)
INNER JOIN Code Code05Code ON PSHeader.Code07 = Code05Code.ID)
INNER JOIN Code Code06Code ON PSHeader.Code08 = Code06Code.ID)
INNER JOIN Code Code07Code ON PSHeader.Code09 = Code07Code.ID)
INNER JOIN Code Code08Code ON PSHeader.Code10 = Code08Code.ID)
INNER JOIN Code Code09Code ON PSHeader.Code11 = Code09Code.ID)
INNER JOIN Code Code10Code ON PSHeader.Code12 = Code10Code.ID)
INNER JOIN Code Code11Code ON PSHeader.Code13 = Code11Code.ID)
WHERE
Profession.ID = 11 and PSData.DateOfService >= '2002/12/01' and
'2002/12/31' >= PSData.DateOfService
AND (PSHeader.SourceOfRequestID = 1 OR PSHeader.SourceOfRequestID = 2 OR
PSHeader.SourceOfRequestID = 3)
and ( StaffCodeID > 0 ) AND ( Code03 > 0 ) AND ( Code04 > 0 ) AND (
Code05 > 0 ) AND
( Code06 > 0 ) AND ( Code07 > 0 ) AND ( Code08 > 0 ) AND ( Code09 >
0 ) AND ( Code10 > 0 ) AND
( Code11 > 0 ) AND ( Code12 > 0 ) AND ( Code13 > 0 )
ORDER BY StaffName ASC, Code01Name ASC, Code02Name ASC, Code03Name ASC,
Code04Name ASC, Code05Name ASC, Code06Name ASC, Code07Name ASC, Code08Name
ASC, Code09Name ASC, Code10Name ASC, Code11Name ASC,
ProfessionPCAC.DisplayOrderPS ASC, ProfessionPCA.DisplayOrderPS ASC
----------------------------------------------------------
SELECT
ProfessionPCAC.ID AS ActivityCategoryID,ProfessionPCAC.Name as CategoryName,
ProfessionPCA.ID as ActivityID,ProfessionPCA.Name as ActivityName,
Profession.Description,
Code.Name AS InOutStatus ,
StaffCode.Name as StaffName , StaffCode.ID as StaffCodeID,
StaffCode.Description as StaffDescription ,
Code01Code.Name as Code01Name , Code01Code.ID as Code01CodeID,
Code01Code.Description as Code01Description ,
Code02Code.Name as Code02Name , Code02Code.ID as Code02CodeID,
Code02Code.Description as Code02Description ,
Code03Code.Name as Code03Name , Code03Code.ID as Code03CodeID,
Code03Code.Description as Code03Description ,
Code04Code.Name as Code04Name , Code04Code.ID as Code04CodeID,
Code04Code.Description as Code04Description ,
Code05Code.Name as Code05Name , Code05Code.ID as Code05CodeID,
Code05Code.Description as Code05Description ,
Code06Code.Name as Code06Name , Code06Code.ID as Code06CodeID,
Code06Code.Description as Code06Description ,
Code07Code.Name as Code07Name , Code07Code.ID as Code07CodeID,
Code07Code.Description as Code07Description ,
Code08Code.Name as Code08Name , Code08Code.ID as Code08CodeID,
Code08Code.Description as Code08Description ,
Code09Code.Name as Code09Name , Code09Code.ID as Code09CodeID,
Code09Code.Description as Code09Description ,
Code10Code.Name as Code10Name , Code10Code.ID as Code10CodeID,
Code10Code.Description as Code10Description ,
Code11Code.Name as Code11Name , Code11Code.ID as Code11CodeID,
Code11Code.Description as Code11Description
FROM
(((((((((((((((((ProfessionPCAC INNER JOIN Profession ON
ProfessionPCAC.ProfessionID = Profession.ID)
INNER JOIN ProfessionPCA ON ProfessionPCAC.ID =
ProfessionPCA.ActivityCategoryID)
INNER JOIN SSData ON ProfessionPCA.ID = SSData.WorkloadFieldID)
INNER JOIN SSHeader ON SSData.HeaderID = SSHeader.HeaderID)
INNER JOIN Code ON SSHeader.SourceOfRequestID = Code.ID)
INNER JOIN Code StaffCode ON SSHeader.StaffCodeID = StaffCode.ID)
INNER JOIN Code Code01Code ON SSHeader.Code03 = Code01Code.ID)
INNER JOIN Code Code02Code ON SSHeader.Code04 = Code02Code.ID)
INNER JOIN Code Code03Code ON SSHeader.Code05 = Code03Code.ID)
INNER JOIN Code Code04Code ON SSHeader.Code06 = Code04Code.ID)
INNER JOIN Code Code05Code ON SSHeader.Code07 = Code05Code.ID)
INNER JOIN Code Code06Code ON SSHeader.Code08 = Code06Code.ID)
INNER JOIN Code Code07Code ON SSHeader.Code09 = Code07Code.ID)
INNER JOIN Code Code08Code ON SSHeader.Code10 = Code08Code.ID)
INNER JOIN Code Code09Code ON SSHeader.Code11 = Code09Code.ID)
INNER JOIN Code Code10Code ON SSHeader.Code12 = Code10Code.ID)
INNER JOIN Code Code11Code ON SSHeader.Code13 = Code11Code.ID)
WHERE
Profession.ID = 11 and SSData.DateOfService >= '2002/12/01' and
'2002/12/31' >= SSData.DateOfService
AND (SSHeader.SourceOfRequestID = 1 OR SSHeader.SourceOfRequestID = 2 OR
SSHeader.SourceOfRequestID = 3)
and ( StaffCodeID > 0 ) AND ( Code03 > 0 ) AND ( Code04 > 0 ) AND (
Code05 > 0 ) AND
( Code06 > 0 ) AND ( Code07 > 0 ) AND ( Code08 > 0 ) AND ( Code09 >
0 ) AND ( Code10 > 0 ) AND
( Code11 > 0 ) AND ( Code12 > 0 ) AND ( Code13 > 0 )
ORDER BY StaffName ASC, Code01Name ASC, Code02Name ASC, Code03Name ASC,
Code04Name ASC, Code05Name ASC, Code06Name ASC, Code07Name ASC, Code08Name
ASC, Code09Name ASC, Code10Name ASC, Code11Name ASC,
ProfessionPCAC.DisplayOrderSS ASC, ProfessionPCA.DisplayOrderSS ASC
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13474@lists.mysql.com
To unsubscribe, e-mail