CODEWITHSUNDEEP
mysqlsqlquery-optimizationprimary-key
Myroslav Tedoski
Myroslav Tedoski

Reputation: 301

Please teach me how to optimize this MySQL query

I have two tables, main_part (3k records) and part_details (25k records)

I tried the following indexes but explain always returns full table scan of 25k records as opposed to about 2k of matched records and Using where; Using temporary; Using filesort

ALTER TABLE `main_part` ADD INDEX `main_part_index_1` (`unit`);
ALTER TABLE `part_details` ADD INDEX `part_details_index_1` (`approved`, `display`, `country`, `id`, `price`);

Here is my query: 

SELECT a.part_id, b.my_title, 
       b.price, a.type, 
       a.unit, a.certification, 
       b.my_image, 
       b.price/a.unit AS priceW 
FROM main_part AS a
INNER JOIN part_details AS b ON a.part_id=b.id
WHERE b.approved = 'Yes' 
AND b.display = 'On' 
AND b.country = 'US' 
AND a.unit >= 300 
ORDER BY b.price ASC LIMIT 50

One thing that I am aware of is that a.part_id is not a Primary Key in main_part table. Could this be a culprit?

Create tables SQL:

CREATE TABLE `main_part` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `part_id` mediumint(9) NOT NULL DEFAULT '0',
  `type` varchar(50) NOT NULL DEFAULT '',
  `unit` varchar(50) NOT NULL DEFAULT '',
  `certification` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `part_details` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `asn` varchar(50) NOT NULL DEFAULT '',
  `country` varchar(5) NOT NULL DEFAULT '',
  `my_title` varchar(200) NOT NULL DEFAULT '',
  `display` varchar(5) NOT NULL DEFAULT 'On',
  `approved` varchar(5) NOT NULL DEFAULT 'No',
  `price` decimal(7,3) NOT NULL DEFAULT '0.000',
  `my_image` varchar(250) NOT NULL DEFAULT '',
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `countryasn` (`country`,`asn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 61

Answers (3)

Tom Shir
Tom Shir

Reputation: 482

It seems that most columns used for filtering in part_details aren't very selective (display is probably an On/off switch, country is probably very similar in many products, etc.).

In some cases, when the WHERE clause is not very selective, MySQL may choose to use an index that better suits the ORDER BY clause.

I would try to create this index as well and check in the explain plan if there is any changes:

ALTER TABLE `part_details` ADD INDEX `part_details_price_index` (`price`);

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

For your query the more important index is the JOIN condition and as you are already aware a.part_id isn't primary key, so doesn't have a default index and your first try should be:

ALTER TABLE `main_part` ADD INDEX `main_part_index_1` (`part_id`,`unit`);

Because we are interested on the JOIN condition first you should also change the second index to 

ALTER TABLE `part_details` ADD INDEX `part_details_index_1` 
            (`id`, `approved`, `display`, `country`, `price`);

order matters in the index

Another tip is you start with the basic query:

SELECT *
FROM main_part AS a
INNER JOIN part_details AS b ON a.part_id=b.id

Add index for part_id and id check the explain plan and then start adding condition and updating the index if required.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269923

For this query:

SELECT mp.part_id, pd.my_title, pd.price, mp.type, 
       mp.unit, mp.certification, pd.my_image, 
       pd.price/mp.unit AS priceW 
FROM main_part mp INNER JOIN
     part_details pd
     ON mp.part_id = pd.id
WHERE pd.approved = 'Yes' AND
      pd.display = 'On' AND
      pd.country = 'US' AND
      mp.unit >= 300 
ORDER BY pd.price ASC
LIMIT 50;

For this query, I would start with indexes on part_details(country, display, approved, id, price) and main_part(part_id, unit).

The index on part_details can be used for filtering before the join. It is not easy to get rid of the sort for the order by.

Upvotes: 0

Related Questions