Weird Performance Issue with Simple Select

[Juan Miguel Paredes]

I'm querying a snort database. There is an event table (see below). I think my query is simple enough. But if I provide a WHERE clause of "sid=21" the query takes a long time (sid 21 has almost 1 million records).

What's really weird is if I provide a WHERE clause of "(sid=21 OR sid=99999)" the query is really fast.

What follows was executed one after the other:

mysql> describe 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 | |
| viewed | tinyint(1) | NO | MUL | 0 | |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select signature, count(*) as total from event where sid=21 and timestamp BETWEEN FROM_UNIXTIME(1114042800) AND FROM_UNIXTIME(1264071100) group by signature;
+-----------+--------+
| signature | total |
+-----------+--------+
| 72 | 1 |
| 115 | 237 |
| 120 | 4 |
| 122 | 825530 |
+-----------+--------+
4 rows in set (12.47 sec) <<------- almost 13 seconds!

mysql> select signature, count(*) as total from event where sid=21 and timestamp BETWEEN FROM_UNIXTIME(1114042800) AND FROM_UNIXTIME(1264071100) group by signature;
+-----------+--------+
| signature | total |
+-----------+--------+
| 72 | 1 |
| 115 | 237 |
| 120 | 4 |
| 122 | 825530 |
+-----------+--------+
4 rows in set (12.54 sec) <<------- almost 13 seconds!

mysql> select signature, count(*) as total from event where (sid=21 or sid=9999) and timestamp BETWEEN FROM_UNIXTIME(1114042800) AND FROM_UNIXTIME(1264071100) group by signature;
+-----------+--------+
| signature | total |
+-----------+--------+
| 72 | 1 |
| 115 | 237 |
| 120 | 4 |
| 122 | 825530 |
+-----------+--------+
4 rows in set (0.96 sec) <<------- Yay!

I don't believe this is a caching issue since I can replicate this consistently. Naturally, a band aid is to always query for multiple "sid" values, but I am wondering what is going on?

Thanks for any help you can provide.