[Tickets #9342] using an index on horde_cache table improve performance

[bugs at horde.org]

BITTE NICHT AUF DIESE NACHRICHT ANTWORTEN. NACHRICHTEN AN DIESE  
E-MAIL-ADRESSE WERDEN NICHT GELESEN.

Ticket-URL: http://bugs.horde.org/ticket/9342
------------------------------------------------------------------------------
  Ticket           | 9342
  Erstellt Von     | michael at bigmichi1.de
  Zusammenfassung  | using an index on horde_cache table improve performance
  Warteschlange    | Horde Framework Packages
  Version          | FRAMEWORK_3
  Typ              | Enhancement
  Status           | New
  Priorität       | 1. Low
  Milestone        |
  Patch            |
  Zuständige      |
------------------------------------------------------------------------------


michael at bigmichi1.de (2010-10-24 03:22) hat geschrieben:

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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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;