Mysql加缓存 刚开始慢,mysql查询起先慢后快

I have 2 myISAM tables, called 'tests' and 'completed_tests', one with 170 entries and the other with 118k entries. When I run this query:

SELECT ct.archive, ct.status, ct.score, ct.users_LOGIN, t.lessons_ID, t.content_ID, t.keep_best

FROM completed_tests ct,tests t

WHERE ct.status != 'deleted' and ct.status != 'incomplete' and t.id=ct.tests_ID and t.lessons_ID=10;

Then it takes around 30 second to accomplish. Subsequent calls to the same query, or related queries (different lessons_ID for example), are much faster. They remain faster even if I reset the query cache or restart the mysql server. I suppose this means that the tables are cached into memory (and stay there).

My problem is that this specific query seems to be causing problems on high traffic sites that run this application (I guess because the server is slow on memory and emptying its cache?).

My questions are:

Is there a way to consistently replicate the 30'' delay on my system, so I can try optimizing the query? Should I, for example, empty my system's cache?

Is there a way to optimize the query above? Running a explain gives:

Running explain gives:

mysql> explain SELECT ct.archive, ct.status, ct.score, ct.users_LOGIN, t.lessons_ID, t.content_ID, t.keep_best FROM completed_tests ct,tests t WHERE ct.status != 'deleted' and ct.status != 'incomplete' and t.id=ct.tests_ID and t.lessons_ID=10;

+----+-------------+-------+------+-----------------+----------+---------+---------------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+-----------------+----------+---------+---------------+------+-------------+

| 1 | SIMPLE | t | ref | PRIMARY,idx1 | idx1 | 3 | const | 4 | |

| 1 | SIMPLE | ct | ref | tests_ID,status | tests_ID | 3 | firstcho.t.id | 1025 | Using where |

+----+-------------+-------+------+-----------------+----------+---------+---------------+------+-------------+

Which, to my understanding, indicates that indexing is used successfully.

Thanks to all.

Table structure

>show create table 'tests';

CREATE TABLE `tests` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`active` tinyint(1) NOT NULL DEFAULT '1',

`content_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',

`lessons_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',

`name` varchar(255) NOT NULL DEFAULT '',

`mastery_score` tinyint(4) unsigned NOT NULL DEFAULT '0',

`description` text,

`options` text,

`publish` tinyint(1) DEFAULT '1',

`keep_best` tinyint(1) DEFAULT '0',

PRIMARY KEY (`id`),

KEY `idx1` (`lessons_ID`)

) ENGINE=MyISAM AUTO_INCREMENT=171 DEFAULT CHARSET=utf8

>show create table completed_tests;

CREATE TABLE `completed_tests` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`users_LOGIN` varchar(100) DEFAULT NULL,

`tests_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',

`test` longblob,

`status` varchar(255) DEFAULT NULL,

`timestamp` int(10) unsigned NOT NULL DEFAULT '0',

`archive` tinyint(1) NOT NULL DEFAULT '0',

`time_start` int(10) unsigned DEFAULT NULL,

`time_end` int(10) unsigned DEFAULT NULL,

`time_spent` int(10) unsigned DEFAULT NULL,

`score` float DEFAULT NULL,

`pending` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `users_login` (`users_LOGIN`),

KEY `tests_ID` (`tests_ID`),

KEY `status` (`status`),

KEY `timestamp` (`timestamp`),

KEY `archive` (`archive`),

KEY `score` (`score`),

KEY `pending` (`pending`)

) ENGINE=MyISAM AUTO_INCREMENT=117996 DEFAULT CHARSET=utf8

解决方案

Finally I resorted to splitting the table to 2, moving the blob object to the second table and joining wherever needed. Unfortunately that involved changing many lines of code.