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:
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.
---------------------------------------------------------------------
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
