I'm in a bit of a tough spot -- My innodb table of WebActionLogs (which
has 1 million rows spread out over the last year and a half) has suddenly
stopped using 'TimeInitiated' to select only the last 24 hours of data
(usually less than 7,500 rows), and now wants to copy all 1 million rows to
a temporary table no matter what I do! I tried adding a 'USE INDEX
(TimeInitiated_ind)', it STILL does the longest possible query by copying
everything to a temporary table first.
Suddenly hundreds of queries that were working fine yesterday and ran under
0.1 seconds are taking 40 to 50 seconds and ending up in the slow query log.
This has been working fine for months.
Is there a way to FORCE innodb to use the index whethere it thinks it's
faster or not? I know it will be 400x faster if it does but USE INDEX
apparently has no effect.
I ran a 'check table WebActionLogs' which sait it was OK. I had a similar
problem when this table was Myisam that was fixed by 'Analyze'ing it but
that's not an available option for Innodb.
mysql> explain SELECT DISTINCT UserName FROM WebActionLogs
USE INDEX (TimeInitiated_ind)
WHERE TimeInitiated > NOW() - INTERVAL 24 HOUR
AND TimeInitiated < NOW() + INTERVAL 300 SECOND
AND Id = 'xx.xx.44.17_3338_1039529682539_1248';
+--------------+-------+-------------------+--------------+---------+------+
---------+------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+--------------+-------+-------------------+--------------+---------+------+
---------+------------+
| WebActionLogs| index | TimeInitiated_ind | UserName_idx | 31 | NULL |
1026114 | where used |
If I remove the DISTINCT, no index is used!!!
mysql> explain SELECT UserTelephoneNumber FROM CallMeBackLogs
-> USE INDEX (TimeInitiated_ind)
-> WHERE TimeInitiated > NOW() - INTERVAL 24 HOUR
-> AND TimeInitiated < NOW() + INTERVAL 300 SECOND
-> AND Guid = 'xx.xx.44.17_3338_1039529682539_1248';
+--------------+------+-------------------+------+---------+------+---------
+------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+--------------+------+-------------------+------+---------+------+---------
+------------+
| WebActionLogs| ALL | TimeInitiated_ind | NULL | NULL | NULL | 1081857
| where used |
+-------------+
| VERSION() |
+-------------+
| 3.23.51-log |
+-------------+
Thanks in advance for any clues!
Joe
---------------------------------------------------------------------
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
