Hi I have this weird problem, any help is much appreciated. I tried a lot of things and searched in the documentation, etc. but haven't found a solution yet.
I have a table 'LogSessions' with a field 'dDateCreation' (datetime type), which has a normal index on it. A simple query like the following uses the index just fine: SELECT COUNT(*) FROM LogSessions WHERE LogSessions.dDateCreation >= '2001-01-01 00:00:00' AND LogSessions.dDateCreation <= '2001-06-30 23:59:59' However, as soon as I add a JOIN it stops using the index (3.5+ million records), I don't understand why. SELECT COUNT(*) FROM LogSessions INNER JOIN Sessions ON LogSessions.iSessionID=Sessions.ID WHERE LogSessions.dDateCreation >= '2001-01-01 00:00:00' AND LogSessions.dDateCreation <= '2001-06-30 23:59:59' An EXPLAIN says this: table type possible_keys key key_len ref rows Extra LogSessions ALL IDX_LogSessions_dDateCreation,IDX_LogSessions_iSessionID 3698297 where used Sessions eq_ref PRIMARY PRIMARY 4 LogSessions.iSessionID 1 Using index It reports the two possible_keys but neither is actually used (key is empty). Another test I did is use this WHERE clause (equal instead of greater-than/smaller-than): WHERE LogSessions.dDateCreation = '2001-01-01 00:00:00' Now the index is used again (even with the JOIN), but of course this is not what I need. Am I missing something? Thanks, Jannes Faber --------------------------------------------------------------------- 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
