Simple performance question - differs on 2 computers
Hi, I have what I believe to be a simple problem that I just can't figure out. I am getting 2 different performance results from the same query on 2 different systems. I am also running 5.1 on 1 machine and 5.0 on the other, but I can't imagine it would make a difference for this simple query.
System 1 - Windows laptop running mysql 5.1
System 2 - Linux desktop running mysql 5.0
I have the exact same schema and a very similar amount of data on both systems (the laptop has a backup of data from the desktop from a few days ago. I have run show index on all the associated tables, and analyze table on all tables on both systems. The only variance between system from what I can see on the indexes appear for this 1 table:
System 1 results
mysql> show index from signature;
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| signature | 0 | PRIMARY | 1 | sig_id | A | 33 | NULL | NULL | | BTREE | |
| signature | 1 | sign_idx | 1 | sig_name | A | NULL | 20 | NULL | | BTREE | |
| signature | 1 | sig_class_id_idx | 1 | sig_class_id | A | NULL | NULL | NULL | | BTREE | |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
System 2 results
mysql> show index from signature;
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| signature | 0 | PRIMARY | 1 | sig_id | A | 36 | NULL | NULL | | BTREE | |
| signature | 1 | sign_idx | 1 | sig_name | A | 36 | 20 | NULL | | BTREE | |
| signature | 1 | sig_class_id_idx | 1 | sig_class_id | A | 7 | NULL | NULL | | BTREE | |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
Here is the actual explain query that I ran:
explain SELECT e.*, ip.*, s.*, d.*,
sig.*, tcp.*, udp.*, icmp.*, opt.* FROM
event e inner join iphdr ip on e.cid = ip.cid and e.sid = ip.sid
inner join sensor s on e.sid = s.sid inner join data d on e.sid =
d.sid and e.cid = d.cid inner join signature sig on e.signature =
sig.sig_id left outer join tcphdr tcp on e.sid = tcp.sid and e.cid =
tcp.cid left outer join udphdr udp on e.sid = udp.sid and e.cid =
udp.cid left outer join icmphdr icmp on e.sid = icmp.sid and e.cid =
icmp.cid left outer join opt opt on e.sid = opt.sid and e.cid =
opt.cid ORDER BY e.timestamp DESC LIMIT 25;
Here is the explain results from system 1
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+------+-------------+
| 1 | SIMPLE | s | system | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | e | index | PRIMARY,sig,idx_sid_cid_timestamp | time | 8 | NULL | 8 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | sig | eq_ref | PRIMARY | PRIMARY | 4 | snort.e.signature | 1 | |
| 1 | SIMPLE | tcp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | udp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | icmp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | opt | ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 3 | |
| 1 | SIMPLE | ip | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.d.cid | 1 | Using where |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+------+-------------+
and here are the explain results from system 2
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+--------+----------------+
| 1 | SIMPLE | s | system | PRIMARY | NULL | NULL | NULL | 1 | Using filesort |
| 1 | SIMPLE | e | range |PRIMARY,sig,idx_sid_cid_timestamp | PRIMARY | 4 | NULL | 528165 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | sig | eq_ref | PRIMARY | PRIMARY | 4 | snort.e.signature | 1 | |
| 1 | SIMPLE | tcp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | udp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | icmp | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 1 | |
| 1 | SIMPLE | opt | ref | PRIMARY | PRIMARY | 8 | const,snort.e.cid | 3 | |
| 1 | SIMPLE | ip | eq_ref | PRIMARY | PRIMARY | 8 | const,snort.d.cid | 1 | Using where |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+--------+----------------+
I can't understand why the results from system 2 show using filesort, and why the rows are 528165 whereas on system 1 filesort is not used and the rows are 8.
Could someone please help?