Hi there,
I'm wondering if someone can help me figure out how the following SELECT can
be improved.
SELECT count(*)
FROM mm_ind_intrst mm0 STRAIGHT_JOIN ind
STRAIGHT_JOIN mm_ind_intrst mm1
WHERE ind.cust=8 AND ind.mail_list=1
AND ( (mm0.intrst='148' AND mm0.ind=ind.id)
AND (mm1.intrst='178' AND mm1.ind=ind.id)
OR ind.email='[EMAIL PROTECTED]' ) ;
The problem seems to come from the OR at the end. The SELECT tries to find
the individuals that:
- belong to customer 8 (ind.cust=8)
- are subscribed to the mailing list (ind.mail_list=1)
- have both interests, 148 and 178.
The interests for a certain individual are stored in the many to many
relationship table, mm_ind_intrst. One row per interest association. Table
description follows:
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | | PRI | NULL | auto_increment |
| ind | int(11) unsigned | | MUL | 0 | |
| intrst | int(11) unsigned | | | 0 | |
+--------+------------------+------+-----+---------+----------------+
The SELECT ran 10 minutes before I finally killed it. I've read the
multiple column index, SELECT speed, and EXPLAIN document pages a few times,
but haven't been able to parse out what needs to be done to fix it. Below
are the results from the EXPLAIN for the above SELECT:
+-------+-------+-----------------------------------------------------------
------------------------+-----------------------------+---------+-----------
--+--------+-------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-------+-------+-----------------------------------------------------------
------------------------+-----------------------------+---------+-----------
--+--------+-------------------------+
| mm0 | index | ind_intrst | ind_intrst | 8 |
NULL | 579730 | Using index |
| ind | ref |
PRIMARY,email,ind_mainindex,email_cust,mail_list_cust,ind_cust_mail_list_id_
email | ind_cust_mail_list_id_email | 5 | const,const | 180226 | where
used; Using index |
| mm1 | index | ind_intrst | ind_intrst | 8 |
NULL | 579730 | where used; Using index |
+-------+-------+-----------------------------------------------------------
------------------------+-----------------------------+---------+-----------
--+--------+-------------------------+
If I modify the SELECT by taking out the OR, the query runs fairly quickly
by comparison, just a few seconds. The modified SELECT and the EXPLAIN
output for that SELECT follow:
SELECT count(*)
FROM mm_ind_intrst mm0 STRAIGHT_JOIN ind
STRAIGHT_JOIN mm_ind_intrst mm1
WHERE ind.cust=8 AND ind.mail_list=1
AND ( (mm0.intrst='148' AND mm0.ind=ind.id)
AND (mm1.intrst='178' AND mm1.ind=ind.id) ) ;
+-------+--------+----------------------------------------------------------
--------+------------+---------+--------------+--------+--------------------
-----+
| table | type | possible_keys
| key | key_len | ref | rows | Extra |
+-------+--------+----------------------------------------------------------
--------+------------+---------+--------------+--------+--------------------
-----+
| mm0 | index | ind_intrst
| ind_intrst | 8 | NULL | 579730 | where used; Using index |
| ind | eq_ref |
PRIMARY,ind_mainindex,mail_list_cust,ind_cust_mail_list_id_email | PRIMARY
| 4 | mm0.ind | 1 | where used |
| mm1 | ref | ind_intrst
| ind_intrst | 8 | ind.id,const | 1 | Using index |
+-------+--------+----------------------------------------------------------
--------+------------+---------+--------------+--------+--------------------
-----+
Can someone tell me why the first SELECT needs to look at so many more rows
than the 2nd? And if there is something that can be done to make it work
better?
Any suggestions are most appreciated and please let me know if more
information is needed.
Thanks,
-Joe
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
