Hi.
On Fri, Nov 16, 2001 at 01:58:54PM -0700, [EMAIL PROTECTED] wrote:
>
> 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]' ) ;
First, with the use of STRAIGHT_JOIN you take away most of the
possibilities for optimization which MySQL has. Why do you use it?
The problem is:
(mm0.intrst='148' AND mm0.ind=ind.id)
AND (mm1.intrst='178' AND mm1.ind=ind.id)
OR ind.email='[EMAIL PROTECTED]')
MySQL isn't able to use indexes for OR in the WHERE clause yet and
therefore the additional OR prevents optimal use of indexes.
Aside from that, if ind.email indeed is [EMAIL PROTECTED], the expression
will evalute to true for _any_ mm0 and mm1 row and therefore you will
get COUNT(mm_ind_intrst)^2 rows as a result (579.730*579.730 =
336.086.872.900), which will indeed take quite some time to gather
together.
So I assume, something with your WHERE condition is flawed.
[...]
> 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 |
> +-------+-------+-----------------------------------------------------------
> ------------------------+-----------------------------+---------+-----------
> --+--------+-------------------------+
[...]
> 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.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
