I have a query as follows:
SELECT log.entity, log.action,
LEFT(users.username,10) AS username,
LEFT(boards.title,15) AS Board,
LEFT(topics.subject,22) as Subject,
log.postid, log.extraid,
LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
LEFT JOIN boards ON log.boardid=boards.id
LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
AND users.username="testuser";
When I run an 'explain' on it, I get:
+--------+--------+---------------+---------+---------+-------------+-------
--+------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+--------+--------+---------------+---------+---------+-------------+-------
--+------------+
| log | ALL | time | NULL | NULL | NULL |
1192384 | where used |
| users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId |
1 | where used |
| boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId |
1 | |
| topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId |
1 | |
+--------+--------+---------------+---------+---------+-------------+-------
--+------------+
4 rows in set (0.01 sec)
Now.. If I exclude the 'users.username="testuser"' and substitute it for the
userid that I got in a previous query (i.e. userid=2) so that the query
becomes:
SELECT log.entity, log.action,
LEFT(users.username,10) AS username,
LEFT(boards.title,15) AS Board,
LEFT(topics.subject,22) as Subject,
log.postid, log.extraid,
LEFT(from_unixtime(log.logtime),19) AS time, log.ip
FROM log LEFT JOIN users ON log.userid = users.id
LEFT JOIN boards ON log.boardid=boards.id
LEFT JOIN topics ON log.topicid = topics.id
WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
AND log.userid=2;
and run an explain, I get...
+--------+--------+---------------+---------+---------+-------------+-------
+------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+--------+--------+---------------+---------+---------+-------------+-------
+------------+
| log | ref | time,userid | userid | 4 | const | 27198
| where used |
| users | eq_ref | PRIMARY | PRIMARY | 4 | log.userId | 1
| |
| boards | eq_ref | PRIMARY | PRIMARY | 4 | log.boardId | 1
| |
| topics | eq_ref | PRIMARY | PRIMARY | 4 | log.topicId | 1
| |
+--------+--------+---------------+---------+---------+-------------+-------
+------------+
4 rows in set (0.00 sec)
Big difference from 1.19million rows to 27198 rows... My question is this.
How can I optimize the query with the left joins so that the optimizer will
first grab the userid from the username and then use the userid index on log
to return the results fast?
Thanks in advance,
David
---------------------------------------------------------------------
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
