do you have an index defined on users.username ?
----- Original Message ----- From: "David Wolf" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, October 28, 2001 4:26 PM Subject: Optimizing query (2nd attempt) > 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 > > --------------------------------------------------------------------- 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