Correct

In H5, horde_cache defines cache_id as an autoincrement key.  This 
(should?) automatically marks this field as a primary key.  (Testing 
on postgresql, attempting to alter the table and add cache_id causes a 
"multiple primary keys for table not allowed" error).

And I don't see how adding a unique index on cache_id and timestamp 
helps us.  We don't need to enforce the combo of those two fields to 
be unique.  And indexing on cache_id should be sufficient - SQL server 
should quickly look up cache_id, and then can quickly compare the 
timestamp field.

In other words, I think our cache table is already sufficiently optimized.

i suggest a primary key index on 'cache_id'

and my optimizer tool also suggest:


./qot --input-file=input.sql --input-query="SELECT cache_data FROM 
horde_cache WHERE cache_id = '7af43c8a03e4cb392e9798b9c259e9d5' AND 
cache_timestamp >= 1287901202;" --info --propose=index
/* Output produced by qot 0.0.4 GPL */
/*
Query: SELECT cache_data FROM horde_cache WHERE cache_id = 
'7af43c8a03e4cb392e9798b9c259e9d5' AND cache_timestamp >= 1287901202

   selectivity:
     zero or one row

   used tables:
     horde_cache (zero or one row)

   ordering:
     no ordering

   existing lookup indexes:
     horde_cache.PRIMARY(cache_id)

   existing covering indexes:
     (none)
*/
/* additional lookup indexes that can be created to improve query 
performance */
CREATE UNIQUE INDEX index0 ON `qot_test`.`horde_cache` (
   cache_id,
   cache_timestamp);



the tool i used is QOT http://ritmark.com/, perhaps you can verify the result

hi,
i enabled log_slow_queries on my mysql server with the 
log-queries-not-using-indexes to get some information for another 
application. in these log i now see also many entries like the 
following:


# User@Host: horde[horde] @ localhost []
# Query_time: 0.000689  Lock_time: 0.000030 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'4ce12d8350d7c0361dc1bf15d552a2d8' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000730  Lock_time: 0.000033 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'90c10b6866449f013adaf54ce5d8c232' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000730  Lock_time: 0.000037 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'5cb3aaa751cbf88cb8eeeaadb403f50e' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000713  Lock_time: 0.000032 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'664858e614367812148716536e22d030' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000692  Lock_time: 0.000031 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'15c13e1ba9355a791b30e0e7f6267761' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000656  Lock_time: 0.000031 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'bbe1562665441d5b0f53764f6f2d10ac' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000685  Lock_time: 0.000030 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'dc417c52e1e565a5d1310322ded358a0' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000671  Lock_time: 0.000031 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'2f382b356de2e770a1989e3db2513c24' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.001333  Lock_time: 0.000030 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'7af43c8a03e4cb392e9798b9c259e9d5' AND cache_timestamp >= 1287901202;
# User@Host: horde[horde] @ localhost []
# Query_time: 0.000794  Lock_time: 0.000027 Rows_sent: 1  Rows_examined: 139
SET timestamp=1287904802;
SELECT cache_data FROM horde_cache WHERE cache_id = 
'b012c8a729fc54a296a700ed92930a0e' AND cache_timestamp >= 1287901202;

then i looked at the table structure and there is no index at all on 
these table

CREATE TABLE IF NOT EXISTS `horde_cache` (
   `cache_id` varchar(32) COLLATE latin1_german1_ci NOT NULL,
   `cache_timestamp` int(11) NOT NULL,
   `cache_expiration` int(11) NOT NULL,
   `cache_data` longblob
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;