sufcrusher,
Monday, September 16, 2002, 11:28:37 PM, you wrote:
s> I have this weird problem, any help is much appreciated. I tried a lot of
s> things and searched in the documentation, etc. but haven't found a solution
s> yet.
s> I have a table 'LogSessions' with a field 'dDateCreation' (datetime type),
s> which has a normal index on it. A simple query like the following uses the
s> index just fine:
s> SELECT COUNT(*)
s> FROM LogSessions
s> WHERE LogSessions.dDateCreation >= '2001-01-01 00:00:00' AND
s> LogSessions.dDateCreation <= '2001-06-30 23:59:59'
s> However, as soon as I add a JOIN it stops using the index (3.5+ million
s> records), I don't understand why.
s> SELECT COUNT(*)
s> FROM LogSessions
s> INNER JOIN Sessions ON LogSessions.iSessionID=Sessions.ID
s> WHERE LogSessions.dDateCreation >= '2001-01-01 00:00:00' AND
s> LogSessions.dDateCreation <= '2001-06-30 23:59:59'
s> An EXPLAIN says this:
s> table type possible_keys key key_len ref rows Extra
s> LogSessions ALL IDX_LogSessions_dDateCreation,IDX_LogSessions_iSessionID
s> 3698297 where used
s> Sessions eq_ref PRIMARY PRIMARY 4 LogSessions.iSessionID 1 Using index
s> It reports the two possible_keys but neither is actually used (key is
s> empty).
s> Another test I did is use this WHERE clause (equal instead of
s> greater-than/smaller-than):
s> WHERE LogSessions.dDateCreation = '2001-01-01 00:00:00'
s> Now the index is used again (even with the JOIN), but of course this is not
s> what I need.
In some cases MySQL doesn't use indexes. How many rows there are in your table? I see
in the first
SELECT MySQL examines 3698297 rows. Is it less than 30% of the rows in the table?
http://www.mysql.com/doc/en/MySQL_indexes.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
