[EMAIL PROTECTED] writes:
> Hello, I have a table of 26 million rows that I need to query. The table
> is a keywords database with the keyword and a pointer to what page it
> came off of. (pageid|keyword) The problem is that when I use the
> following query is the group by make the query take about 14 seconds to
> do. However, if I remove the group by portion it finishes the query in
> less then .10.
>
> Here is the query:
> SELECT count(pageid) as n, pageid FROM keywords left join
> deleted_keywords on keywords.keyid = deleted_keywords.keyid where
> deleted_keywords.keyid is null and (keyword = 'Big' OR keyword =
> 'Brown') group by pageid having n = 2 limit 100;
I recognize this query... I'll give you the answer for a papajohns.com
pizza. ;-)
Anyways, looks like Jeremy and Sasha were discussing the optimization
of `OR' in your query on our internal IRC network last night. Looking
at the IRC log, Sasha recommended that you should use:
"... keyword IN('Big', 'Brown') group by ..."
instead of the OR statement. This helps MySQL to decide to use the
index on 'keyword'.
Then Sasha talked with Monty about it, and asked about the
optimization of OR in a query. Monty said that the only time OR is not
optimized is if the OR happens on a different key or a non-key. So
this kind of adds to the confusion. :)
Can you try the "keyword IN()" as I mentioned above. If this doesn't
work, can you tell us the specifics of your setup -- MySQL version,
OS, etc (mysqlbug stuff). Actually mysqlbug output would probably be
desired, as this *may* be a bug in the optimizer. Also, please include
the output from EXPLAIN on all the queries you have tried.
http://www.mysql.com/doc/E/X/EXPLAIN.html
Regards,
Matt
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Matt Wagner <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Saint Paul, Minnesota, USA
<___/ Developer
---------------------------------------------------------------------
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
