Mysql query optimizations using indexes

Question

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;

For articles table, state column has values like "PUBLISHED" or "UNPUBLISHED" and type column has values like "NEWS", "GOSSIP" and "OPINION".

My application performs a lot of queries like this:

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 I am trying to determine whether the query above performs better with the default foreign key on category, or I should use a multi-column index instead.

Without an index (using "explain extended" ):

+----+-------------+-------+------+---------------------------------+---------------------------------+---------+-------+------+-----------------------------+
| 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 the multi-column index and explain again (forcing the 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 the query actually returns is 630. It seems to me that the multi-column index should perform better than the FK since all indexed columns are used, but the fact that ~1200 rows are examined when using the index confuses me. I know that these numbers are just estimations, but the difference between the two keys is pretty big; with the combined index, we have the double amount of rows examined.

So my questions are the following:

1. Why are so many rows examined with the multi-column index?
2. Since using an FK we have a join type "ref" and using the combined index we have a join type "range", does this mean that the query that uses the FK is better/faster than the other one?
3. Should I use the estimation for the number of rows examined as a criteria to decide if an index is good/optimal?
4. In this use-case, is the multi-column index better that the FK? On what basis should i make the decision?

Some additional information:

• Without forcing an index on the query, optimizer chose the FK. When I performed an analyze table on articles, the multi-column index was chosen 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.

Answer 1

As you can see index on publishedAt has the same cardinality as PK. This doesn't really help. I would try to create a compound index with columns in that order (category,type,state). This way, the first part of the index is the most selective.