Hi!
I have this table :
mysql> desc cpu;
+-----------------+----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------------------+------+-----+---------------------+-------+
| timecode | datetime | | MUL | 0000-00-00 00:00:00 | |
| systemid | smallint(5) unsigned | | | 0 | |
| usertime | float(3,1) | YES | | NULL | |
| systemtime | float(3,1) | YES | | NULL | |
| waittime | float(3,1) | YES | | NULL | |
| idletime | float(3,1) | YES | | NULL | |
| runqueue60 | float(4,2) | YES | | NULL | |
| processes | smallint(5) unsigned | YES | | NULL | |
| interrupts | float(6,1) | YES | | NULL | |
| systemcalls | float(6,1) | YES | | NULL | |
| contextswitches | float(6,1) | YES | | NULL | |
+-----------------+----------------------+------+-----+---------------------+-------+
11 rows in set (0.00 sec)
With this index :
mysql> show index from cpu;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| cpu | 1 | timecode | 1 | timecode | A | 4199933 |
NULL | NULL | |
| cpu | 1 | timecode | 2 | systemid | A | 4199933 |
NULL | NULL | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.13 sec)
...and I execute a query like this (here with explain first) :
mysql> explain SELECT substring_index(elisa.timecode,':',1),
avg(elisa.usertime + elisa.systemtime) AS elisa,
avg(asuncion10.usertime + asuncion10.systemtime) AS asuncion10,
FROM cpu AS elisa, cpu AS asuncion10, cpu AS asuncion20
WHERE elisa.systemid='44'
AND asuncion10.systemid='47'
AND substring_index(elisa.timecode,':',1) =
substring_index(asuncion10.timecode,':',1)
AND elisa.timecode > now() - INTERVAL 7 DAY
AND asuncion10.timecode > now() - INTERVAL 7 DAY
GROUP BY substring_index(elisa.timecode,':',1)
ORDER BY substring_index(elisa.timecode,':',1);
+------------+-------+---------------+----------+---------+------+--------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------------+-------+---------------+----------+---------+------+--------+-----------------------------+
| elisa | range | timecode | timecode | 8 | NULL | 612892 | where used;
Using temporary |
| asuncion10 | range | timecode | timecode | 8 | NULL | 612892 | where used
|
+------------+-------+---------------+----------+---------+------+--------+-----------------------------+
2 rows in set (0.02 sec)
This query takes quite a long time to execute, and I'm sure it can be done more elegant
and faster....just don't know how.
Anyone ?
It's MySQL 3.23.49 with InnoDB tables.
/Jesper
