Optimizing MySQL table structure

Question

I'm a MySQL newbie. I have a table with the following structure:

CREATE TABLE `gc_ads` (
  `n` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  `detail` text NOT NULL,
  `c` char(6) DEFAULT NULL,
  `c_path` char(24) DEFAULT NULL,
  `a` char(8) DEFAULT NULL,
  `a_path` char(32) DEFAULT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `edited` int(10) unsigned NOT NULL DEFAULT '0',
  `t1` int(10) unsigned NOT NULL DEFAULT '0',
  `t2` int(10) unsigned NOT NULL DEFAULT '0',
  `status` set('waiting','enabled','disabled','queue','wait','preview') NOT NULL DEFAULT '',
  `en_cats` tinyint(1) NOT NULL DEFAULT '0',
  `home_page` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`n`,`t1`,`t2`,`status`,`en_cats`),
  KEY `home_page_featured` (`status`,`home_page`),
  KEY `recount_index` (`c_path`,`a_path`,`t1`,`t2`,`status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I need to run the following query:

select * from gc_ads where c_path in ("_9_","_9_30_","_9_101_","_9_102_","_9_103_","_9_105_","_9_106_","_9_30_132_","_9_30_133_","_9_30_134_","_9_30_135_","_9_30_136_","_9_30_137_","_9_30_138_","_9_30_139_","_9_30_140_","_9_30_141_","_9_30_142_","_9_30_143_","_9_30_144_","_9_30_145_","_9_30_146_","_9_30_147_","_9_30_148_","_9_30_149_","_9_30_150_","_9_30_151_","_9_30_152_","_9_30_153_","_9_30_154_","_9_30_155_","_9_30_156_","_9_30_157_","_9_30_158_","_9_30_159_","_9_30_160_","_9_30_161_","_9_30_162_","_9_101_222_","_9_101_221_","_9_101_220_","_9_101_219_","_9_101_218_","_9_101_217_","_9_101_216_","_9_101_215_","_9_101_214_","_9_101_213_","_9_101_212_","_9_101_211_","_9_101_210_","_9_101_209_","_9_101_208_","_9_101_207_","_9_101_206_","_9_101_205_","_9_101_204_","_9_101_203_","_9_101_202_","_9_101_201_","_9_101_200_","_9_101_199_","_9_101_198_","_9_101_196_","_9_101_197_","_9_101_195_","_9_194_","_9_101_223_","_9_101_224_","_9_101_225_","_9_102_226_","_9_102_227_","_9_102_228_","_9_102_229_","_9_102_230_","_9_102_231_","_9_102_232_","_9_102_233_","_9_102_234_","_9_102_235_","_9_102_236_","_9_102_237_","_9_102_238_","_9_102_239_","_9_102_240_","_9_102_241_","_9_102_242_","_9_102_243_","_9_102_244_","_9_102_245_","_9_102_246_","_9_102_247_","_9_102_248_","_9_102_249_","_9_102_250_","_9_102_251_","_9_102_252_","_9_102_253_","_9_102_254_","_9_102_255_","_9_102_256_") and status='enabled' order by created desc limit 0,50;

This query takes 0.38s on about 60,000 records. I think this is very slow. Esplain shows the following:

+----+-------------+--------+------+----------------------------------+--------------------+---------+-------+-------+-----------------------------+
| id | select_type | table  | type | possible_keys                    | key                | key_len | ref   | rows  | Extra                       |
+----+-------------+--------+------+----------------------------------+--------------------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | gc_ads | ref  | recount_index,home_page_featured | home_page_featured | 1       | const | 34347 | Using where; Using filesort |
+----+-------------+--------+------+----------------------------------+--------------------+---------+-------+-------+-----------------------------+

If I run this query (same query, but without "and status='enabled'" in where, it takes about 0.10s:

select * from gc_ads use index(recount_index, home_page_featured) where c_path in ("_9_","_9_30_","_9_101_","_9_102_","_9_103_","_9_105_","_9_106_","_9_30_132_","_9_30_133_","_9_30_134_","_9_30_135_","_9_30_136_","_9_30_137_","_9_30_138_","_9_30_139_","_9_30_140_","_9_30_141_","_9_30_142_","_9_30_143_","_9_30_144_","_9_30_145_","_9_30_146_","_9_30_147_","_9_30_148_","_9_30_149_","_9_30_150_","_9_30_151_","_9_30_152_","_9_30_153_","_9_30_154_","_9_30_155_","_9_30_156_","_9_30_157_","_9_30_158_","_9_30_159_","_9_30_160_","_9_30_161_","_9_30_162_","_9_101_222_","_9_101_221_","_9_101_220_","_9_101_219_","_9_101_218_","_9_101_217_","_9_101_216_","_9_101_215_","_9_101_214_","_9_101_213_","_9_101_212_","_9_101_211_","_9_101_210_","_9_101_209_","_9_101_208_","_9_101_207_","_9_101_206_","_9_101_205_","_9_101_204_","_9_101_203_","_9_101_202_","_9_101_201_","_9_101_200_","_9_101_199_","_9_101_198_","_9_101_196_","_9_101_197_","_9_101_195_","_9_194_","_9_101_223_","_9_101_224_","_9_101_225_","_9_102_226_","_9_102_227_","_9_102_228_","_9_102_229_","_9_102_230_","_9_102_231_","_9_102_232_","_9_102_233_","_9_102_234_","_9_102_235_","_9_102_236_","_9_102_237_","_9_102_238_","_9_102_239_","_9_102_240_","_9_102_241_","_9_102_242_","_9_102_243_","_9_102_244_","_9_102_245_","_9_102_246_","_9_102_247_","_9_102_248_","_9_102_249_","_9_102_250_","_9_102_251_","_9_102_252_","_9_102_253_","_9_102_254_","_9_102_255_","_9_102_256_") order by created desc limit 0,50;

Explain shows the following:

+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-----------------------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows  | Extra                       |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | gc_ads | range | recount_index | recount_index | 73      | NULL | 14293 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-----------------------------+

And if I run the query without "order by ...", it's blazing fast (0.01s):

select * from gc_ads use index(recount_index, home_page_featured) where c_path in ("_9_","_9_30_","_9_101_","_9_102_","_9_103_","_9_105_","_9_106_","_9_30_132_","_9_30_133_","_9_30_134_","_9_30_135_","_9_30_136_","_9_30_137_","_9_30_138_","_9_30_139_","_9_30_140_","_9_30_141_","_9_30_142_","_9_30_143_","_9_30_144_","_9_30_145_","_9_30_146_","_9_30_147_","_9_30_148_","_9_30_149_","_9_30_150_","_9_30_151_","_9_30_152_","_9_30_153_","_9_30_154_","_9_30_155_","_9_30_156_","_9_30_157_","_9_30_158_","_9_30_159_","_9_30_160_","_9_30_161_","_9_30_162_","_9_101_222_","_9_101_221_","_9_101_220_","_9_101_219_","_9_101_218_","_9_101_217_","_9_101_216_","_9_101_215_","_9_101_214_","_9_101_213_","_9_101_212_","_9_101_211_","_9_101_210_","_9_101_209_","_9_101_208_","_9_101_207_","_9_101_206_","_9_101_205_","_9_101_204_","_9_101_203_","_9_101_202_","_9_101_201_","_9_101_200_","_9_101_199_","_9_101_198_","_9_101_196_","_9_101_197_","_9_101_195_","_9_194_","_9_101_223_","_9_101_224_","_9_101_225_","_9_102_226_","_9_102_227_","_9_102_228_","_9_102_229_","_9_102_230_","_9_102_231_","_9_102_232_","_9_102_233_","_9_102_234_","_9_102_235_","_9_102_236_","_9_102_237_","_9_102_238_","_9_102_239_","_9_102_240_","_9_102_241_","_9_102_242_","_9_102_243_","_9_102_244_","_9_102_245_","_9_102_246_","_9_102_247_","_9_102_248_","_9_102_249_","_9_102_250_","_9_102_251_","_9_102_252_","_9_102_253_","_9_102_254_","_9_102_255_","_9_102_256_") limit 0,50;

Explain shows the following:

+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows  | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-------------+
|  1 | SIMPLE      | gc_ads | range | recount_index | recount_index | 73      | NULL | 14293 | Using where |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-------------+    

I'm guessing that I need to create the correct index for this query, but I really have no idea how to begin tackling this problem.

Answer 1

A rule of thumb is to create indexes on the columns, that will be used for comparison (WHERE section, JOIN sections etc) in most queries. This is also true for columns that are included in most complicated queries (i.e. multiple joins) which are run on a regular basis.

Taking a look at your SQL, I think the best solution would be creating an index on 3 columns altogether: 'c_path' and 'status', as those are the columns, that take part in the comparison), and also 'created' (as it holds the values being the sorting criteria).

Including a column from ORDER BY query part in index is not always sensible, but in your case it seems quite reasonable. I recommend reading more about indexing and ORDER BY here - it explains why it sometimes makes sense and sometimes doesn't.

Answer 2

This is your query:

select *
from gc_ads
where c_path in (. . .) and status='enabled'
order by created desc limit 0,50;

Your conditions in the where clause are an in and equality. The best index would be gc_ads(status, c_path, created). The where clause can use the first two elements. The order by can use the last one.

The recount_index is not quite right, because it has two additional elements before status.