Thanks for your reply! I had read about the 30% too, however in the SELECT query without the INNER JOIN the EXPLAIN result is this:
table type possible_keys key key_len ref rows Extra LogSessions range IDX_LogSessions_dDateCreation IDX_LogSessions_dDateCreation 8 812548 where used; Using index 800,000 / 3,600,000 < 30% Also (I'm not an expert on the details) I imagine an index on a datetime field as a sorted list or tree. So I assume specifying boundaries in my WHERE clause ( LogSessions.dDateCreation >= '2001-01-01 00:00:00' AND LogSessions.dDateCreation <= '2001-06-30 23:59:59' ) should drastically reduce the number of records (without much overhead) that need to be checked/joined, no matter if it results in 10%, 30% or 50% ? In the meantime, I did find out that it's not the JOIN that causes the problem, if I add another WHERE clause, it also stops using the index. In fact this seems even more weird to me because the extra field (iActionType) I check now _also_ has an index of it's own. For honesty's sake I should add that this last index is probably not really effective because only '1' and '2' are valid values for iActionType, the majority however are 1, so I'd say that's a lot of records that can be skipped. WHERE LogSessions.dDateCreation >= '2001-01-01 00:00:00' AND LogSessions.dDateCreation <= '2001-06-30 23:59:59' AND LogSessions.iActionType=2 BTW: I also tried using 'USE INDEX (...)' and 'IGNORE INDEX (...)' stuff, doesn't help anything though. Hmm... I just tried to adjust the dates to get the results over a smaller period (2 months instead of half a year): then it starts using the index again.The flip-over point seems to be around 600,000 records, about 20%. I'll go do some timings to see if it's really slower, maybe it was just my impression. Thanks again! Jannes Faber ----- Original Message ----- From: "Egor Egorov" <[EMAIL PROTECTED]> Sent: Tuesday, September 17, 2002 4:10 PM Subject: re: Mysql doesn't use datetime index? > sufcrusher, > Monday, September 16, 2002, 11:28:37 PM, you wrote: > > 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 > --------------------------------------------------------------------- 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
