Re: Index recomendations

[laptop alias]

Sometimes an index can actually be counterproductive...

SELECT COUNT(*) FROM tab1;
+----------+
| COUNT(*) |
+----------+
|  2097152 |
+----------+

-- Test 1. No index...

SELECT COUNT(no)
  FROM tab1
 WHERE version='1.1.1.1' 
   AND rel_date BETWEEN ADDDATE(NOW(), INTERVAL -1 HOUR) AND NOW();
+-----------+
| COUNT(no) |
+-----------+
|       397 |
+-----------+
1 row in set (0.68 sec)


-- Test 2. Index on version...

ALTER TABLE tab1 ADD INDEX (version);

SELECT COUNT(no)
  FROM tab1
 WHERE version='1.1.1.2' 
   AND rel_date BETWEEN ADDDATE(NOW(), INTERVAL -1 HOUR) AND NOW();
+-----------+
| COUNT(no) |
+-----------+
|       609 |
+-----------+
1 row in set (2.13 sec) -- 3 times slower than 1!!!!!!!!!!

-- Test 3. Composite index on (version,rel_date)

ALTER TABLE tab1 ADD INDEX version_reldate (version,rel_date);

SELECT COUNT(no)
  FROM tab1
 WHERE version='1.1.1.3' 
   AND rel_date BETWEEN ADDDATE(NOW(), INTERVAL -1 HOUR) AND NOW();
+-----------+
| COUNT(no) |
+-----------+
|       736 |
+-----------+
1 row in set (0.01 sec) -- 68 times faster than 1!!!!!!!!!!!!!

... a more exhaustive test would compare a dense dataset as against a sparse one but I'll leave that for others to explore.

So, for now, try adding a composite index on (version,rel_date).



Edited 1 time(s). Last edit at 11/09/2010 07:20PM by laptop alias.