My db schema consists of the following two tables:
CREATE TABLE `categories` ( `id` bigint(20) NOT NULL auto_increment, `title` varchar(128) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and
CREATE TABLE `articles` ( `id` bigint(20) NOT NULL auto_increment, `title` varchar(512) NOT NULL, `body` longtext, `state` varchar(7) NOT NULL, `type` varchar(6) NOT NULL, `category` bigint(20) default NULL, `publishedAt` datetime default NULL, PRIMARY KEY (`id`), KEY `FK_category_to_article_category` (`category`), CONSTRAINT `FK_category_to_article_category` FOREIGN KEY (`category`) REFERENCES `categories` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
In the article table, the state column has values, such as the "PUBLISHED" or "UNPUBLISHED" columns, and the type columns have values, such as "NEWS", "GOSSIP" and "OPINION".
My application performs many requests, for example:
select * from articles where state="PUBLISHED" and type in ("NEWS","GOSSIP") and category in (4) and publishedAt<=now() order by publishedAt desc;
I have ~ 10K articles and am trying to determine if the query above works with the default foreign key in the category, or should I use an index with multiple columns instead.
Without an index (using "explain advanced"):
+----+-------------+-------+------+---------------------------------+---------------------------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------------+---------------------------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | this_ | ref | FK_category_to_article_category | FK_category_to_article_category | 9 | const | 630 | Using where; Using filesort | +----+-------------+-------+------+---------------------------------+---------------------------------+---------+-------+------+-----------------------------+
If I create an index with multiple columns and explain again (forcing a specific index):
create index I_s_t_c_p on articles (state, type, category, publishedAt); +----+-------------+-------+-------+---------------+-----------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | this_ | range | I_s_t_c_p | I_s_t_c_p | 61 | NULL | 1216 | Using where; Using index; Using filesort | +----+-------------+-------+-------+---------------+-----------+---------+------+------+------------------------------------------+
The number of rows that the query actually returns is 630. It seems to me that an index with several columns should work better than FK, since all indexed columns are used, but the fact that ~ 1200 rows is checked when using the index confuses me. I know that these numbers are only estimates, but the difference between the two keys is quite large; with a combined index, we have double the number of rows tested.
So my questions are:
- Why are so many rows checked with a multi-column index?
- Since we use FK, we have the join type “ref”, and using the combined index we have a “range” of join type, does this mean that a query that uses FK is better / faster than the other?
- Should I use an estimate of the number of rows considered as a criterion for determining the optimal / optimal index?
- In this use case, is an index of several columns better than FK? On what basis should I make a decision?
Additional Information:
- Without forcing the index on demand, the optimizer chose FK. When I did
analyze table for articles, an index with multiple columns was selected instead. - I am using MySql 5.0.15
- index information
+----------+------------+---------------------------------+--------------+-------------+-------------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality | Index_type | +----------+------------+---------------------------------+--------------+-------------+-------------+------------+ | articles | 0 | PRIMARY | 1 | id | 12561 | BTREE | | articles | 1 | FK_category_to_article_category | 1 | category | 37 | BTREE | | articles | 1 | I_s_t_c_p | 1 | state | 8 | BTREE | | articles | 1 | I_s_t_c_p | 2 | type | 32 | BTREE | | articles | 1 | I_s_t_c_p | 3 | category | 163 | BTREE | | articles | 1 | I_s_t_c_p | 4 | publishedAt | 12561 | BTREE | +----------+------------+---------------------------------+--------------+-------------+-------------+------------+
Thanks in advance.