Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
On Thu, Feb 25, 2010 at 8:48 AM, Dan Nelson dnel...@allantgroup.com wrote: IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely efficient); it's subqueries in general that are killers. If the dependent subquery is nothing but index lookups, it's still blazingly fast, though :) I just optimized one like that: select nid from search_total left join search_index on search_total.nid = search_index.nid where search_index.nid is null; got optimized to select nid from search_total where nid not in (select nid from search_index); This shaved 3 seconds off a 10-second query (field is indexed in both tables, plenty of room in the key cache). Now, if there was a way to tell MySQL that the subquery isn't dependant, it should turn into a near-zero query. I also tested a *not exists* construct, which turned out to be about a hundreth of a second slower. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
On Thu, Feb 25, 2010 at 2:48 AM, Dan Nelson dnel...@allantgroup.com wrote: IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely efficient); Yes, I meant to say IN/NOT IN subqueries, not value lists. it's subqueries in general that are killers. Subqueries in the FROM clause (aka derived tables) work pretty well, acting as an in-line temp table. Other subqueries perform poorly, as you say. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
Hello, On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang yanghates...@gmail.com wrote: I have the following query. Note that the nested query has no dependencies on the outer one, yet mysql reports it as dependent. Do an EXPLAIN EXTENDED followed by SHOW WARNINGS. You will see the optimization that mysqld applies to the subquery, to try to help it by adding a dependency on the outer query. There's nothing you can do about this :-( You have to use a JOIN in most cases. BTW, the general log is itself a performance killer when logged to tables. If I were you I'd use the slow query log and mk-query-digest from Maatkit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
EXPLAIN says DEPENDENT SUBQUERY despite no free variables
I have the following query. Note that the nested query has no dependencies on the outer one, yet mysql reports it as dependent. Furthermore, it says the join type is an ALL (nested loop join, the slowest possible one, in which each row of the outer table results in a complete inner table scan), whereas I know that the subquery yields only 50 tuples, so a const join would've made more sense. Any ideas on how to optimize this by convincing mysql to see the independence use a const join? (This is in mysql 5.4.3 beta.) Thanks in advance. mysql explain select thread_id, argument, event_time from general_log where command_type in (Query, Execute) and thread_id in ( select distinct thread_id from general_log where ( (command_type = Init DB and argument like tpcc50) or (command_type = Connect and argument like %tpcc50) ) and thread_id 0 ) order by thread_id, event_time desc; +++-+--+---+--+-+--+---+--+--+ | id | select_type| table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +++-+--+---+--+-+--+---+--+--+ | 1 | PRIMARY| general_log | ALL | NULL | NULL | NULL| NULL | 335790898 | 100.00 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | general_log | ALL | NULL | NULL | NULL| NULL | 335790898 | 100.00 | Using where; Using temporary | +++-+--+---+--+-+--+---+--+--+ 2 rows in set, 1 warning (0.04 sec) -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang yanghates...@gmail.com wrote: Any ideas on how to optimize this by convincing mysql to see the independence use a const join? http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ You need to rewrite as a join or use a FROM subquery. You should pretty much always avoid using IN/NOT IN. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
In the last episode (Feb 24), Perrin Harkins said: On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang yanghates...@gmail.com wrote: Any ideas on how to optimize this by convincing mysql to see the independence use a const join? http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ You need to rewrite as a join or use a FROM subquery. You should pretty much always avoid using IN/NOT IN. IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely efficient); it's subqueries in general that are killers. Current MySQL versions almost always treat subqueries as dependent, even ones that are obviously not. The 6.0 branch was a significant improvement, but that branch has been killed off, and there's no indication of the fixes being backported to 5.x . -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
