Slow MySQL query

I have 3 tables:

Name                  Engine  Rows    Data Size   Index Size  Total Size
product               MyISAM  11.06M  859.59M     861.20M     1.68G
product_manufacturer  MyISAM  3.09K   236.52K     367K        603.52K
product_source        MyISAM  4       88          3K          3.09K

DDL for product:

`CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(54) COLLATE utf8_unicode_ci NOT NULL,
  `number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `number_cleaned` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  `created` date NOT NULL,
  `modified` date NOT NULL,
  `source_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_product_3aac1984` (`number_cleaned`),
  KEY `product_product_4ac7f441` (`manufacturer_id`),
  KEY `product_product_425ae3c4` (`group_id`),
  KEY `product_7607617b` (`source_id`),
  KEY `product_65da3d2c` (`slug`),
  KEY `product_ec9ad377` (`modified`),
  KEY `product_4264c638` (`is_active`)
) ENGINE=MyISAM AUTO_INCREMENT=11637660 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

SQL that runs ~35sec. Why?

SELECT `product`.`id` FROM `product` 
INNER JOIN `product_source` 
    ON `product`.`source_id` = product_source`.`id` 
WHERE `product`.`number_cleaned` = '5404'

EXPLAIN Result:

state duration (summed) in sec percentage
Sending data 35.77627 99.99776 (!!!)
Opening tables 0.00037 0.00103
statistics 0.00014 0.00039
updating status 0.00009 0.00025
starting 0.00005 0.00014
preparing 0.00003 0.00008
optimizing 0.00003 0.00008
init 0.00003 0.00008
freeing items 0.00001 0.00003
Table lock 0.00001 0.00003

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  product_source  index   PRIMARY PRIMARY 4   (NULL)  4   Using index
1   SIMPLE  product ref product_product_3aac1984,product_7607617b   product_7607617b    4   product_source.id  1   Using where

But other SQL runs very quick:

SELECT `product`.`id` FROM `product` 
INNER JOIN `product_manufacturer` 
    ON `product`.`manufacturer_id` = `product_manufacturer`.`id` 
WHERE `product`.`number_cleaned` = '5404'

EXPLAIN Result:

state   duration (summed) in sec    percentage
Sending data    0.00023 41.81818
statistics  0.00009 16.36364
updating status 0.00006 10.90909
starting    0.00005 9.09091
init    0.00002 3.63636
Opening tables  0.00002 3.63636
preparing   0.00002 3.63636
optimizing  0.00002 3.63636
Table lock  0.00001 1.81818
checking permissions    0.00001 1.81818
Unlocking tables    0.00001 1.81818
System lock 0.00001 1.81818


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  product ref product_product_3aac1984,product_product_4ac7f441   product_product_3aac1984    152 const   13  Using index condition
1   SIMPLE  product_manufacturer    eq_ref  PRIMARY PRIMARY 4   product.manufacturer_id 1   Using index

Switch to InnoDB to avoid table locks. Table locks can tie up MyISAM tables for a long time.

product: INDEX(number_cleaned, manufacturer_id, id)
product: INDEX(number_cleaned, source_id, id)

number and number_cleaned sound like numeric values, yet you declared them VARCHAR. Be sure to always quote the numbers you compare them against.

I would aim for an index on:

(number_cleaned, source_id)

i.e.

KEY ... (number_cleaned, source_id)

I never understood why MySQL uses the term key for something that’s not, but that’s a different story. As @mysql_user points out, investigate whether it’s possible to migrate your tables to innodb.

If you want to stretch things a bit further you can create a covering index like:

KEY ... (number_cleaned, source_id, id)