Hi!
I have this table :
mysql> describe proc;
+-----------------+-----------------------+------+-----+--------------------
-+-------+
| Field | Type | Null | Key | Default
| Extra |
+-----------------+-----------------------+------+-----+--------------------
-+-------+
| timecode | datetime | | MUL | 0000-00-00 00:00:00
| |
| system_id | smallint(5) unsigned | | | 0
| |
| pid | char(8) | | |
| |
| ppid | char(8) | | |
| |
| username | char(20) | | |
| |
| cpu_usage | float(4,2) | YES | | NULL
| |
| memory_physical | mediumint(8) unsigned | YES | | NULL
| |
| memory_virtual | mediumint(8) unsigned | YES | | NULL
| |
| usertime | float(4,2) | YES | | NULL
| |
| systemtime | float(4,2) | YES | | NULL
| |
| priority | tinyint(4) | YES | | NULL
| |
| input_block | mediumint(8) unsigned | YES | | NULL
| |
| output_block | mediumint(8) unsigned | YES | | NULL
| |
| major_fault | mediumint(8) unsigned | YES | | NULL
| |
| minor_fault | mediumint(8) unsigned | YES | | NULL
| |
| processname | char(20) | | |
| |
+-----------------+-----------------------+------+-----+--------------------
-+-------+
With these indexes :
mysql> show index from proc;
+-------+------------+-------------+--------------+-------------+-----------
+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Comment |
+-------+------------+-------------+--------------+-------------+-----------
+-------------+----------+--------+---------+
| proc | 1 | timecode | 1 | timecode | A
| NULL | NULL | NULL | |
| proc | 1 | system_id | 1 | system_id | A
| NULL | NULL | NULL | |
+-------+------------+-------------+--------------+-------------+-----------
+-------------+----------+--------+---------+
A common select wouldbe something like this :
mysql> explain SELECT timecode,usertime,systemtime FROM proc WHERE
(system_id = '1') AND timecode BETWEEN '2001-03-23 11:08:11' AND '2001-03-24
11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY
timecode;
+-------+-------+--------------------+----------+---------+------+------+---
---------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+--------------------+----------+---------+------+------+---
---------+
| proc | range | timecode,system_id | timecode | 8 | NULL | 5394 |
where used |
+-------+-------+--------------------+----------+---------+------+------+---
---------+
Or with a bigger timespan like this :
mysql> explain SELECT timecode,usertime,systemtime FROM proc WHERE
(system_id = '1') AND timecode BETWEEN '2001-03-13 11:08:11' AND '2001-03-24
11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY
timecode;
+-------+------+--------------------+------+---------+------+--------+------
----------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+--------------------+------+---------+------+--------+------
----------------------+
| proc | ALL | timecode,system_id | NULL | NULL | NULL | 206878 | where
used; Using filesort |
+-------+------+--------------------+------+---------+------+--------+------
----------------------+
With the same SQL, but with index added on processname :
mysql> explain SELECT timecode,usertime,systemtime FROM proc WHERE
(system_id = '1') AND timecode BETWEEN '2001-03-13 11:08:11' AND '2001-03-24
11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY
timecode;
+-------+------+--------------------------------+-------------+---------+---
----+-------+----------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-------+------+--------------------------------+-------------+---------+---
----+-------+----------------------------+
| proc | ref | timecode,system_id,processname | processname | 30 |
const | 16237 | where used; Using filesort |
+-------+------+--------------------------------+-------------+---------+---
----+-------+----------------------------+
Now, the problem is that I have about 30 system_id's in the database. Each
system insert about 500 rows into the proc table every 10 minute.
...so after a month or so I have alot of rows (> 100 million), and a
performance problem.
In Oracle I have fixed this by partitioning on system_id and/or timecode,
but this is not possible to do transparently in MySQL, and redesigning the
database structure with one set of tables or one database for each system
will get very ugly.
...so the result now is that MySQL is pathetically slow and Oracle extremely
fast, in fact more than 30 times faster.
I have tried to combine the indexes, but then MySQL fails using them
properly, so the best I've been able to come up with is seperate index on
timecode,system_id and processname. Then it seems that MySQL decides to use
whatever is best for the selected timescope.
I've also had isamchk to sort the data tables according to timecode, since
the result is allways ordered by timecode.
Have I forgotten something, or are the current indexes as good as it gets ?
--
Un saludo / Venlig hilsen / Regards
Jesper Frank Nemholt
Unix System Manager
Compaq Computer Corporation
Phone : +34 699 419 171
E-Mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
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
