In SQL how can I use an index to speed up this database query?

Viewed 236

I have two tables:

enter image description here

CREATE TABLE `album` (
  `album_id` int(10) unsigned NOT NULL,
  `artist_id` int(10) unsigned NOT NULL,
  `album_type_id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `first_released` year(4) NOT NULL,
  `country_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`album_id`),
  KEY `artist_id` (`artist_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB

CREATE TABLE `artist` (
  `artist_id` int(10) unsigned NOT NULL,
  `type` enum('Band','Person','Unknown','Combination') NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` enum('Male','Female') DEFAULT NULL,
  `founded` year(4) DEFAULT NULL,
  `country_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`artist_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB;

My first query:

select ar.name, count(*)   
from artist ar 
join album al on ar.artist_id=al.artist_id   
group by ar.artist_id  
limit 10;

which produces a result set in 0.00 seconds.

I would like to order the results, so I add "order by 2 desc":

select ar.name, count(*)  
from artist ar  
join album al on ar.artist_id=al.artist_id  
group by ar.artist_id  
order by 2 desc  
limit 10;

but now the result set takes over 5 seconds, which is too slow.

Execution plan: enter image description here

How can I add an index to speed up my new query?

Sample data: http://bit.ly/1k5ROrT

1 Answers
Related