How to optimize a MySQL JOIN Query

Question

I have this MySQL query that I want to optimize:

SELECT r.WarehouseLocation,sum(sir.qty) 
FROM repairableissue as r 
INNER JOIN SIR ON r.sirno=sir.sirno 
    AND r.region=sir.region 
    AND r.ItemName=sir.Itemdesc 
    AND r.SerialNo=sir.Serialno
WHERE r.status='Pending' 
GROUP BY r.warehouseLocation

How do I optimize this query? I read about optimization and found out that indexes might help but still could not achieve the desired performance.

Which index should be used and which should be removed?

Below is the explain of query: enter image description here

Repairableissue

CREATE TABLE `repairableissue` (
 `Vendor` varchar(40) NOT NULL,
 `ItemName` varchar(200) NOT NULL,
 `SerialNo` varchar(50) NOT NULL,
 `person` varchar(200) NOT NULL,
 `siteid` varchar(10) NOT NULL,
 `invuser` varchar(50) NOT NULL,
 `region` varchar(50) NOT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `Dated` date NOT NULL,
 `Sirno` varchar(50) NOT NULL,
 `status` varchar(30) NOT NULL DEFAULT 'Pending',
 `trackthrough` varchar(30) NOT NULL,
 `reason` varchar(100) NOT NULL,
 `ckh` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `WarehouseType` varchar(20) NOT NULL,
 `WarehouseLocation` varchar(20) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `I1` (`status`),
 KEY `ind2` (`ItemName`),
 KEY `ind3` (`region`),
 KEY `ind5` (`SerialNo`),
 KEY `ind4` (`Sirno`)
) ENGINE=MyISAM AUTO_INCREMENT=63029 DEFAULT CHARSET=latin1

sir

CREATE TABLE `sir` (
 `SirNo` varchar(50) NOT NULL,
 `SiteId` varchar(80) NOT NULL,
 `Vendor` varchar(70) NOT NULL,
 `Type` varchar(15) NOT NULL,
 `ItemDesc` varchar(200) NOT NULL,
 `ItemCode` varchar(25) NOT NULL,
 `SerialNo` varchar(50) NOT NULL,
 `Unit` varchar(15) NOT NULL,
 `AssetCode` varchar(50) NOT NULL,
 `Qty` decimal(11,0) NOT NULL,
 `Region` varchar(15) NOT NULL,
 `Status` varchar(20) NOT NULL DEFAULT 'Installed',
 `FaultInfo` varchar(100) NOT NULL DEFAULT 'date()',
 `chk` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `Phase` varchar(15) NOT NULL,
 `Category` varchar(200) NOT NULL,
 `Issue_Vendor` varchar(30) NOT NULL,
 `AssetName` varchar(150) NOT NULL,
 `Ownership` varchar(20) NOT NULL,
 `Dated` date NOT NULL,
 `PersonName` varchar(150) NOT NULL,
 `Remarks` varchar(300) NOT NULL,
 `po` varchar(100) NOT NULL,
 `invuser` varchar(50) NOT NULL,
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `grnno` varchar(30) NOT NULL,
 `WarehouseType` varchar(20) NOT NULL,
 `WarehouseLocation` varchar(20) NOT NULL,
 `mainpartserial` varchar(200) NOT NULL,
 PRIMARY KEY (`Vendor`,`Type`,`ItemCode`,`ItemDesc`,`SerialNo`,`Ownership`,`SirNo`,`Region`,`WarehouseType`,`WarehouseLocation`,`po`,`Qty`,`id`),
 KEY `id` (`id`),
 KEY `ind4` (`ItemDesc`),
 KEY `ind6` (`SerialNo`),
 KEY `ind7` (`SerialNo`)
) ENGINE=MyISAM AUTO_INCREMENT=228007 DEFAULT CHARSET=latin1

i just accepted some edit and that was removed :) added again — Muhammad Haseeb Khan
– Muhammad Haseeb Khan, Commented Jan 7, 2014 at 17:24

Marcus Adams · Accepted Answer · 2014-01-07 17:34:11Z

1

One multi-column index on r.status + r.warehouseLocation, in that order.

One multi-column index on sir.sirno + sir.region + sir.Itemdesc + sir.Serialno, in order of most cardinality to least cardinality, with sir.qty tacked on the end.

This assumes the fields are small enough to fit (combined) into an index.

Still, join seeks are unavoidable. The number of records that match r.status='Pending' is going to dictate the speed of this query.

edited Jan 7, 2014 at 17:34

answered Jan 7, 2014 at 17:28

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

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

5 Comments

Muhammad Haseeb Khan Over a year ago

i have created indexes on each but sorry don't know the compound indexes

Marcus Adams Over a year ago

@MuhammadHaseebKhan, I updated my answer with MySQL specific terminology and a link. Take a look.

Muhammad Haseeb Khan Over a year ago

O my GOD! it did the trick. one question are these index could effect my other queries on these tables. seriously it is owsome

Muhammad Haseeb Khan Over a year ago

can you please guide me some resource where i can learn abut technique of using indexes?

Marcus Adams Over a year ago

@MuhammadHaseebKhan, indexes can negatively impact inserts. Remove any unused indexes.

Collectives™ on Stack Overflow

How to optimize a MySQL JOIN Query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related