Reputation: 10188
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:
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
Upvotes: 5
Views: 4715
Reputation: 10188
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.
Upvotes: 0
Reputation: 7025
Regarding the other users on high traffic sites that run this and have problems, it is entirely possible that they have database configuration issues outside of your control. That aside, here's some recommendations that would help.
Improving performance
The following assumes a 1:n relationship from tests:completed_tests based on t.id:ct.tests_id where there will always be a row in tests present for n number of rows in completed_tests.
The following additional index is advisable to aid the join
CREATE INDEX `ct_to_tests` ON completed_tests (tests_id,status);
Furthermore, if you are able to change ct.status to be ENUM('deleted','status',..... any other possibilieis ....)
(assuming a limited number of statuses available, which is entirely viable) then that too will increase performance as it'll remove the only text search.
The reason I suggest ENUM
is simple. status
looks to be a field with definition VARCHAR(255)
which is populated programmatically and as such will have a limited number of discrete values. If you are able to change the VARCHAR
into an ENUM
then MySQL will be able to treat it as if it's a numeric field. This is becuase behind the scenes each string in an ENUM
is given a numeric index, and it's that index which is used when matching on an ENUM
instead of a full string when using VARCHAR
, which in turn is far more efficient.
SELECT
t.lessons_ID,
t.content_ID,
t.keep_best,
ct.archive,
ct.status,
ct.score,
ct.users_LOGIN
FROM tests t
INNER JOIN completed_tests ct
ON ct.status NOT IN ('deleted,'status')
AND ct.tests_id = t.id
WHERE t.lessons_ID = 10
Upvotes: 2
Reputation: 3055
I would start by trying to find out exactly what the problem is.
Do you have phpmyadmin on the server where the problem occurs? If so, you could submit the query to the "sql" tab with the "profiling" checkbox checked. You'll get a verbose trace of the entire query execution, and maybe that will give you some insight into what you need to solve or how to replicate the problem. Also, try adding indexes to all the columns that you mention in your where clause, if you haven't already done this.
Upvotes: 0
Reputation: 54084
try with LEFT JOIN
SELECT ct.archive, ct.status, ct.score, ct.users_LOGIN,
t.lessons_ID, t.content_ID, t.keep_best
FROM completed_tests ct
LEFT JOIN tests t ON (t.id=ct.tests_ID)
WHERE t.lessons_ID=10 AND ct.status != 'deleted' AND ct.status != 'incomplete' ;
Upvotes: 0