List:General Discussion« Previous MessageNext Message »
From:Jonathan Terhorst Date:August 11 2008 12:26pm
Subject:Query optimization help
View as plain text  
I have this query:

SELECT DISTINCT t1.string FROM t1
	LEFT JOIN t2 ON t1.string=t2.string
	LEFT JOIN t3 ON t1.int_a=t3.int_a
	LEFT JOIN t4 ON t1.int_b=t4.int_b
WHERE
	t1.string != '' AND
	t2.string IS NULL AND
	t3.int_a = <some integer>
ORDER BY
	t1.string ASC

This query is executing slower than it should. EXPLAIN has it using  
temporary and using filesort.
I have indexes on every column in the query, but I think the problem  
is the one-index-per-table limitation. According to EXPLAIN, there are  
two possible indices in use for t1 (int_a and string), but only int_a  
is being used. So I tried constructing a compound index on int_a and  
string. Although this new index appears in possible_keys, EXPLAIN  
still shows the key actually being used as int_a. I tried building the  
compound key in both orders and had the same results. How do get mysql  
to all possible keys on t1 when running the query? Thanks!


Thread
Query optimization helpJonathan Terhorst11 Aug
  • Re: Query optimization helpBrent Baisley13 Aug