CODEWITHSUNDEEP
mysqldatabase
Sami M'Barek
Sami M'Barek

Reputation: 137

MySQL Order by slow

I've got a barnyard2 and snort running.

I'm trying to join 3 tables, which in it self works fine. It's when ordering the select by cid it slows down really bad. Here you see the two select and the time between them.

Running without order by:

mysql> SELECT event.sid, event.cid, event.timestamp, iphdr.ip_src, iphdr.ip_dst, 
      signature.sig_name 
      FROM event 
      INNER JOIN iphdr 
      ON event.cid=iphdr.cid 
      INNER JOIN signature 
      ON event.signature=signature.sig_id 
      WHERE sig_name like 'ICMP%' limit 10;
<.....>
10 rows in set (0.02 sec)

Running with order by:

mysql> SELECT event.sid, event.cid, event.timestamp, iphdr.ip_src, iphdr.ip_dst, 
          signature.sig_name 
       FROM event 
       INNER JOIN iphdr 
       ON event.cid=iphdr.cid 
       INNER JOIN signature 
       ON event.signature=signature.sig_id 
       WHERE sig_name like 'ICMP%' 
       order by event.cid desc limit 10;
<.....>
10 rows in set (6 min 1.52 sec)

desc event;

 +-----------+------------------+------+-----+---------+-------+
 | Field     | Type             | Null | Key | Default | Extra |
 +-----------+------------------+------+-----+---------+-------+
 | sid       | int(10) unsigned | NO   | PRI | NULL    |       |
 | cid       | int(10) unsigned | NO   | PRI | NULL    |       |
 | signature | int(10) unsigned | NO   | MUL | NULL    |       |
 | timestamp | datetime         | NO   | MUL | NULL    |       |
 +-----------+------------------+------+-----+---------+-------+
 4 rows in set (0.00 sec)

desc signature;

 +--------------+------------------+------+-----+---------+----------------+
 | Field        | Type             | Null | Key | Default | Extra          |
 +--------------+------------------+------+-----+---------+----------------+
 | sig_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
 | sig_name     | varchar(255)     | NO   | MUL | NULL    |                |
 | sig_class_id | int(10) unsigned | NO   | MUL | NULL    |                |
 | sig_priority | int(10) unsigned | YES  |     | NULL    |                |
 | sig_rev      | int(10) unsigned | YES  |     | NULL    |                |
 | sig_sid      | int(10) unsigned | YES  |     | NULL    |                |
 | sig_gid      | int(10) unsigned | YES  |     | NULL    |                |
 +--------------+------------------+------+-----+---------+----------------+
 7 rows in set (0.00 sec)

desc iphdr;

 +----------+----------------------+------+-----+---------+-------+
 | Field    | Type                 | Null | Key | Default | Extra |
 +----------+----------------------+------+-----+---------+-------+
 | sid      | int(10) unsigned     | NO   | PRI | NULL    |       |
 | cid      | int(10) unsigned     | NO   | PRI | NULL    |       |
 | ip_src   | int(10) unsigned     | NO   | MUL | NULL    |       |
 | ip_dst   | int(10) unsigned     | NO   | MUL | NULL    |       |
 | ip_ver   | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_hlen  | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_tos   | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_len   | smallint(5) unsigned | YES  |     | NULL    |       |
 | ip_id    | smallint(5) unsigned | YES  |     | NULL    |       |
 | ip_flags | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_off   | smallint(5) unsigned | YES  |     | NULL    |       |
 | ip_ttl   | tinyint(3) unsigned  | YES  |     | NULL    |       |
 | ip_proto | tinyint(3) unsigned  | NO   |     | NULL    |       |
 | ip_csum  | smallint(5) unsigned | YES  |     | NULL    |       |
 +----------+----------------------+------+-----+---------+-------+
 14 rows in set (0.00 sec)

It's been a long long time since I worked in MySQL so my querying isn't that good.

Upvotes: 1

Views: 1137

Answers (1)

Nanne
Nanne

Reputation: 64409

It seems you don't have an index on the event.cid. It is part of the primary key, but if it is second in that key it can not be used to sort. Ergo, your order-by is slow.

If you look at the EXPLAIN, you can probably see it. Please do an EXPLAIN, then add an index for that column, and check the EXPLAIN again. It will probably help :)

Upvotes: 1

Related Questions