David Wolf wrote:
> Maybe I'm missing something here--I don't know
> of a way to create an index
> on TWO tables at once? Also, when I do:
My bad. You cant.
Since you are joining on the primary key, you want
to add an index like:
ALTER TABLE users ADD UNIQUE ( id, username(10));
Since you are using a left-join and a criteria on
a table that is left-joined, it can be difficult
to get a good index.
b.
(Sorry to spam you for a second time, David)
>
>
> EXPLAIN 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 users.username="testuser";
>
> (users.username is indexed), I get the
> following:
>
> +--------+
> -------+---------------+---------+---------+-------------+-------
>
> --+------------+
> | table | type | possible_keys | key |
> key_len | ref | rows
> | Extra |
> +--------+----
> ---+---------------+---------+---------+-------------+-------
>
> --+------------+
> | log | ALL | NULL | NULL |
> NULL | NULL |
> 1199187 | |
> | 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.00 sec)
>
> It's just simply not using the index on users..
> Did I miss something?
>
> David
>
> --
> -- Original Message -----
> From: "Bill Adams" <[EMAIL PROTECTED]>
> To: "David Wolf" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Monday, October 29, 2001 9:21 AM
> Subject: Re: Optimizing query (2nd attempt)
>
> > 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.
--
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
