David Wolf wrote:
>Not quite fixed.. When I run the query without limiting by time, it still
>fails to use the userid key. i.e. if I only select where
>users.username="testuser", I'd expect that users.username to return the
>users.id=2, and to search using the indexed log.userid=2
MySQL can only use one index on a table at a time. It also uses the columns in
the order in which they are defined. ORDER MATTERS!
The manual does not seem to cover this, but at least Informix will stop using
an index when an inequality is hit. E.g.: if you have an index on (a, b, c )
and the query has WHERE a=5 AND b>2 AND c=10, the only part of the index that
will be used is (a, b). (Monty & co, is this true with MySQL? Can you add
something to the manual either way?)
So assuming this is true in your where clause:
> WHERE log.logTime > UNIX_TIMESTAMP("2000-10-26 23:00:00")
> AND users.username="testuser";
If you have an index on ( logTime, username), since you have an inequality for
lotTime in the query, username will NOT be used. However if you have the index
on (username, logTime) --or even just the first 10 chars or so of username +
logTime-- then both username AND logTime will be used in the index.
You may want to try this to see if it makes any difference.
And, of course, run myisamchk -a on the tables after you build indexes.
b.
> > ----- 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
> >
> >
>
> ---------------------------------------------------------------------
> 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
--
Bill Adams
TriQuint Semiconductor
---------------------------------------------------------------------
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
