Description:
I try to get the last 5 different times and the first 5
different times. There is a difference of aproximately
1 minute and 40 seconds between this to queries. I do not see
reason for the difference in the execution times.
I am the only one who works on the database. Nobody else has acces
to it. The times shown in the examples does not vary. There are
always the same. There are no other programs aktiv on my machine.
It is not a problem of workload or network traffic.
I have looked on the mailing list but I could not find something
helpfull. There was no mysqlbug script in the mysql directories.
So I tried to collect all the necessary information as described
in the documentation.
I tried to reproduce the behaviour with smaller tables but it
was not possible.
I know that I do not need the Time index but I tried it to see
if the timing changes. It does not. There is no other reason for
this index.
-- MySQL dump 8.17
--
-- Host: localhost Database: snoopertest7
---------------------------------------------------------
-- Server version 4.0.0-alpha-nt
--
-- Table structure for table 'eventlist'
--
CREATE TABLE eventlist (
ID double default NULL,
eventTime char(25) default NULL,
sameTimeCounter char(3) default NULL,
interface char(10) default NULL,
telegramType char(30) default NULL,
telegramID double default NULL,
KEY TelegramTypeIndex (telegramType),
KEY TelegramIDIndex (telegramID),
KEY eventTimeIndex (eventTime,sameTimeCounter,ID),
KEY Time (eventTime)
) TYPE=MyISAM;
How-To-Repeat:
mysql> select distinct eventTime from eventList where
eventTime < '99999999999999' order by eventTime desc limit 0,5;
+------------------+
| eventTime |
+------------------+
| 000617153001 030 |
| 000617153000 970 |
| 000617153000 910 |
| 000617153000 880 |
| 000617153000 850 |
+------------------+
5 rows in set (1 min 39.89 sec)
mysql> explain select distinct eventTime from eventList where
eventTime < '99999999999999' order by eventTime desc limit 0,5;
+-----------+-------+---------------------+------+---------+
------+---------+------------------------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-----------+-------+---------------------+------+---------+
------+---------+------------------------------------------+
| eventList | range | eventTimeIndex,Time | Time | 26 |
NULL | 3418451 | where used; Using index; Using temporary |
+-----------+-------+---------------------+------+---------+
------+---------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select distinct eventTime from eventList where
eventTime > '0' order by eventTime asc limit 0,5;
+------------------+
| eventTime |
+------------------+
| 000617102959 970 |
| 000617103000 030 |
| 000617103000 100 |
| 000617103000 160 |
| 000617103000 220 |
+------------------+
5 rows in set (0.00 sec)
mysql> explain select distinct eventTime from eventList where
eventTime > '0' order by eventTime asc limit 0,5;
+-----------+-------+---------------------+------+---------+
------+---------+----------------------------------------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra
|
+-----------+-------+---------------------+------+---------+
------+---------+----------------------------------------------------------+
| eventList | range | eventTimeIndex,Time | Time | 26 |
NULL | 3418459 | where used; Using index; Using temporary; Using filesort
|
+-----------+-------+---------------------+------+---------+
------+---------+----------------------------------------------------------+
1 row in set (0.00 sec)
Fix:
<how to correct or work around the problem, if known (multiple
lines)>
Synopsis: Speed problem with sorted select statements
Submitter-Id: <submitter ID>
Originator: Denny Fritzsche
Organization: Alcatel Canada
MySQL support: none
Severity: serious
Priority: high
Category: mysqld
Class: sw-bug
Release: mysql-4.0.0-alpha-nt
Exectutable: mysqld-nt
Environment: Compaq Deskpro EN, x86 Family 6 Model 8 Stepping 3, 256 MB
RAM, i815 Chip Set
System: NT4 SP6
Compiler:
Architecture: i
---------------------------------------------------------------------
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
