Simple MySQL table executing 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, for example:

SELECT
    *   
FROM
    content_link
WHERE
    category_id = '11';

mysql starts the processor and takes 2-5 seconds before returning about 10 rows. The data is distributed very evenly throughout the table, and I get access to the indexed fields (I also analyzed / optimized the table, and I never change the contents of the table), so what reason is the request takes so long?

Edit: It seems that navicat was lying to me, and my primary key was not actually entered in the correct order, as it showed me the table.

Answer 1

category_id .

:

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

, .

Answer 2

UNIQUE KEY - , .

, MySQL . , .

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

MySQL 5.1.

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

Answer 3

. , (content_id, category_id), content_id, content_id category_id. category_id .

:

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

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