Simple MySQL Table Running Slow Queries

Question

I have a very simple table with two columns, but has 4.5M rows.

CREATE TABLE `content_link` (
  `category_id` mediumint(8) unsigned NOT NULL,
  `content_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`content_id`,`category_id`),
  KEY `content_id` (`content_id`,`category_id`)
) ENGINE=MyISAM;

When I run a simple query like:

SELECT
    * 
FROM
    content_link
WHERE
    category_id = '11';

mysql spikes the CPU and takes 2-5 seconds before returning about 10 rows. The data is spread very evenly across the table and I'm accessing indexed fields (I've also analyzed/optimized the table and I never change the content of the table), so what reason is there for the query to take so long?

Edit: It seems navicat was lying to me and my primary key was not actually keyed in the right order as it was displaying the table to me.

Answer 1

category_id is not the first column in any index.

Recreate your secondary key as follows:

UNIQUE KEY `ix_category_id` (`category_id`, `content_id`)

Note the column order, it matters.

Answer 2

You are not using the index. When you have a composite index like (content_id, category_id) you can use the index by using content_id or you can use content_id and category_id. You can't use category_id and utilize the index.

Try changing:

KEY `content_id` (`content_id`, `category_id`)

to

KEY `category_id` (`category_id`, `content_id`)

Answer 3

The UNIQUE KEY ordering is a good solution and you should add partition strategy to you table too.

By partition the table in fragments, MySQL will query the specific partition with the rigth data set. I've applied and I had excelent results.

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=INNODB PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6;

You need MySQL 5.1.

Try http://dev.mysql.com/doc/refman/5.1/en/partitioning.html