First, you might want to move the WHERE...t3.int_a = <some integer>
condition into the join condition for t3.
Your not using anything from t4, so I'm not sure why you have that
table in your query.
You can suggest or force mysql to use an index if it's using the wrong
one:
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
On very rare occasions I've had to do this. What's happening is that
mysql is analyzing the information it has about the data and indexes
and coming to the wrong conclusion, perhaps even opting for an entire
table scan. You can run ANALYZE TABLE to force mysql to update the
information it has about the data. This may actually solve your problem.
Try SHOW INDEX FROM t1 to see what data mysql has about the indexes.
Sometimes the CARDINALITY (uniqueness) column will be null which can
indicate a problem.
Posting the result of your EXPLAIN will actually be helpful.
Hope that helps.
Brent Baisley
On Aug 11, 2008, at 8:26 AM, Jonathan Terhorst wrote:
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!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
