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.

what do you mean by spread evenly? Is the result correct? Have you profiled the I/O? Is there hardware latency? — Richard
– Richard, Commented May 29, 2009 at 13:45
By spread evenly I meant I don't have the ID's clumped together, so a mysql BTREE index shouldn't have any problems with it. — Ian
– Ian, Commented May 29, 2009 at 13:54

Quassnoi · Accepted Answer · 2009-05-29 13:45:07Z

8

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.

answered May 29, 2009 at 13:45

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Over a year ago

That's really weird. I'm using navicat for table creation, and strangely it shows me the columns in the order I wanted, but apparently this table create query is showing them in a different order.. It's actually the primary key that's in the wrong order since I have category id before content_id, they should be keyed in that order.

C B · Accepted Answer · 2017-09-27 19:40:50Z

2

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

By partitioning the table in fragments, MySQL will query the specific partition with the right data set. I've applied and I had excellent 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

edited Sep 27, 2017 at 19:40

C B

1,7176 gold badges20 silver badges20 bronze badges

answered Jun 10, 2009 at 22:39

Eduardo Xavier

1,55021 silver badges29 bronze badges

Comments

Travis Beale · Accepted Answer · 2009-05-29 13:50:17Z

1

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`)

answered May 29, 2009 at 13:50

Travis Beale

5,6987 gold badges36 silver badges34 bronze badges

Collectives™ on Stack Overflow

Simple MySQL Table Running Slow Queries

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related