Hi,
I have a query that is executing rather slowly and I'm stuck as to how I
can speed it up. I'm open to offers!
Basically, the SQL is generated automatically and extra clauses are added
for every keyword that the user enters (DataFieldValue in the SQL). With
only 1 or 2 keywords, the SQL executes pretty quickly; but when I get to
three keywords, it sloooows down.
Here's the SQL for one keyword:
select
e.Ref,
e.File_Reference as "File Ref.",
e.DateTaken as "Date",
u.Description as "Unit",
et.Description as "Type",
el.Description as "Lang Pref."
from
Enquiry e
join Unit u
join EnquiryType et
join EnquiryLanguage el
join Enquiry_Contact_ContactRole ecr0
join SXIndex sx0
where
e.CurrentUnit_Ref = u.Ref
and
e.Type_ref = et.Ref
and
e.Language_Ref = el.Ref
and
ecr0.Enquiry_Ref = e.Ref
and
(
(
sx0.DataFieldValue = 'COLIN'
and
sx0.Table_Ref = 1
and
sx0.KeyFieldValue = ecr0.Contact_Ref
and
sx0.IndexType_Ref in (0,2)
and
sx0.DataFieldNumber in (7, 6, 8, 10, 11, 12, 13, 14, 15, 16)
)
)
This produces the following explain output:
+-------+--------+--------------------------------------+----------------+
---------+-------------------+------+-------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-------+--------+--------------------------------------+----------------+
---------+-------------------+------+-------+
| sx0 | ref | PRIMARY,DataFieldValue,KeyFieldValue | DataFieldValue
| 10 | COLIN | 58 | |
| ecr0 | ref | PRIMARY,Contact_Ref | Contact_Ref
| 4 | sx0.KeyFieldValue | 1 | |
| e | eq_ref | PRIMARY | PRIMARY
| 4 | ecr0.Enquiry_Ref | 1 | |
| el | eq_ref | PRIMARY | PRIMARY
| 1 | e.Language_Ref | 1 | |
| u | eq_ref | PRIMARY | PRIMARY
| 1 | e.CurrentUnit_Ref | 1 | |
| et | eq_ref | PRIMARY | PRIMARY
| 1 | e.Type_Ref | 1 | |
+-------+--------+--------------------------------------+----------------+
---------+-------------------+------+-------+
Everything looks fine and dandy so far and this query returns 106 results
in 0.12 sec.
However, things appear to start going wrong when I add in another
keyword:
select
e.Ref,
e.File_Reference as "File Ref.",
e.DateTaken as "Date",
u.Description as "Unit",
et.Description as "Type",
el.Description as "Lang Pref."
from
Enquiry e
join Unit u
join EnquiryType et
join EnquiryLanguage el
join Enquiry_Contact_ContactRole ecr0
join SXIndex sx0
join Enquiry_Contact_ContactRole ecr1
join SXIndex sx1
where
e.CurrentUnit_Ref = u.Ref
and
e.Type_ref = et.Ref
and
e.Language_Ref = el.Ref
and
ecr0.Enquiry_Ref = e.Ref
and
ecr1.Enquiry_Ref = e.Ref
and
(
(
sx0.DataFieldValue = 'COLIN'
and
sx0.Table_Ref = 1
and
sx0.KeyFieldValue = ecr0.Contact_Ref
and
sx0.IndexType_Ref in (0,2)
and
sx0.DataFieldNumber in (7, 6, 8, 10, 11, 12, 13, 14, 15, 16)
)
and
(
sx1.DataFieldValue = 'ENGLAND'
and
sx1.Table_Ref = 1
and
sx1.KeyFieldValue = ecr1.Contact_Ref
and
sx1.IndexType_Ref in (0,2)
and
sx1.DataFieldNumber in (7, 6, 8, 10, 11, 12, 13, 14, 15, 16)
)
)
Here's the output from explain for this query:
+-------+--------+--------------------------------------+----------------+
---------+-------------------------+------+-------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-------+--------+--------------------------------------+----------------+
---------+-------------------------+------+-------------+
| sx0 | ref | PRIMARY,DataFieldValue,KeyFieldValue | DataFieldValue
| 10 | COLIN | 58 | |
| ecr0 | ref | PRIMARY,Contact_Ref | Contact_Ref
| 4 | sx0.KeyFieldValue | 1 | |
| e | eq_ref | PRIMARY | PRIMARY
| 4 | ecr0.Enquiry_Ref | 1 | |
| el | eq_ref | PRIMARY | PRIMARY
| 1 | e.Language_Ref | 1 | |
| u | eq_ref | PRIMARY | PRIMARY
| 1 | e.CurrentUnit_Ref | 1 | |
| et | eq_ref | PRIMARY | PRIMARY
| 1 | e.Type_Ref | 1 | |
| sx1 | range | PRIMARY,DataFieldValue,KeyFieldValue | DataFieldValue
| NULL | NULL | 66 | |
| ecr1 | eq_ref | PRIMARY,Contact_Ref | PRIMARY
| 8 | e.Ref,sx1.KeyFieldValue | 1 | Using index |
+-------+--------+--------------------------------------+----------------+
---------+-------------------------+------+-------------+
8 rows in set (0.04 sec)
I don't understand why the "range" join appears for table sx1. Why
doesn't this appear as a "ref" join like sx0?
This query returns 13 rows in 1.25 seconds, quite an increase in
execution time.
The real problems start when I get to 3 keywords. Here's the SQL:
select
e.Ref,
e.File_Reference as "File Ref.",
e.DateTaken as "Date",
u.Description as "Unit",
et.Description as "Type",
el.Description as "Lang Pref."
from
Enquiry e
join Unit u
join EnquiryType et
join EnquiryLanguage el
join Enquiry_Contact_ContactRole ecr0
join SXIndex sx0
join Enquiry_Contact_ContactRole ecr1
join SXIndex sx1
join Enquiry_Contact_ContactRole ecr2
join SXIndex sx2
where
e.CurrentUnit_Ref = u.Ref
and
e.Type_ref = et.Ref
and
e.Language_Ref = el.Ref
and
ecr0.Enquiry_Ref = e.Ref
and
ecr1.Enquiry_Ref = e.Ref
and
ecr2.Enquiry_Ref = e.Ref
and
(
(
sx0.DataFieldValue = 'COLIN'
and
sx0.Table_Ref = 1
and
sx0.KeyFieldValue = ecr0.Contact_Ref
and
sx0.IndexType_Ref in (0,2)
and
sx0.DataFieldNumber in (7, 6, 8, 10, 11, 12, 13, 14, 15, 16)
)
and
(
sx1.DataFieldValue = 'ENGLAND'
and
sx1.Table_Ref = 1
and
sx1.KeyFieldValue = ecr1.Contact_Ref
and
sx1.IndexType_Ref in (0,2)
and
sx1.DataFieldNumber in (7, 6, 8, 10, 11, 12, 13, 14, 15, 16)
)
and
(
sx2.DataFieldValue = 'GUARDIAN'
and
sx2.Table_Ref = 1
and
sx2.KeyFieldValue = ecr2.Contact_Ref
and
sx2.IndexType_Ref in (0,2)
and
sx2.DataFieldNumber in (7, 6, 8, 10, 11, 12, 13, 14, 15, 16)
)
)
And here's the explain results:
+-------+--------+--------------------------------------+----------------+
---------+-------------------------+------+-------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-------+--------+--------------------------------------+----------------+
---------+-------------------------+------+-------------+
| sx1 | ref | PRIMARY,DataFieldValue,KeyFieldValue | DataFieldValue
| 10 | ENGLAND | 66 | |
| ecr1 | ref | PRIMARY,Contact_Ref | Contact_Ref
| 4 | sx1.KeyFieldValue | 1 | |
| e | eq_ref | PRIMARY | PRIMARY
| 4 | ecr1.Enquiry_Ref | 1 | |
| el | eq_ref | PRIMARY | PRIMARY
| 1 | e.Language_Ref | 1 | |
| u | eq_ref | PRIMARY | PRIMARY
| 1 | e.CurrentUnit_Ref | 1 | |
| et | eq_ref | PRIMARY | PRIMARY
| 1 | e.Type_Ref | 1 | |
| sx2 | range | PRIMARY,DataFieldValue,KeyFieldValue | DataFieldValue
| NULL | NULL | 54 | |
| sx0 | range | PRIMARY,DataFieldValue,KeyFieldValue | DataFieldValue
| NULL | NULL | 58 | |
| ecr2 | eq_ref | PRIMARY,Contact_Ref | PRIMARY
| 8 | e.Ref,sx2.KeyFieldValue | 1 | Using index |
| ecr0 | eq_ref | PRIMARY,Contact_Ref | PRIMARY
| 8 | e.Ref,sx0.KeyFieldValue | 1 | Using index |
+-------+--------+--------------------------------------+----------------+
---------+-------------------------+------+-------------+
10 rows in set (0.45 sec)
Now I've got *two* "range" joins.
This query returns 5 rows in 2min 15.97 sec - totally unacceptable
performance.
I have an idea that this slow down is something to do with the "range"
joins but I don't see why they are happening.
Has anyone any idea what is wrong? Michael?
Thanks,
R.
--
Robin Bowes, System Development Manager, Equal Opportunities Commission,
Room 405A, Overseas House, Quay St., Manchester, M3 3HN, UK.
Tel: +44 (0) 161 838 8321 Fax: +44 (0) 161 835 1657
Lord, grant me the serenity to accept the things I cannot change,
the courage to change the things I can, and the wisdom to hide the
bodies of the people I had to kill because they pissed me off - Anon.
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
