Optimize mysql query that take long execution time about 4 minutes

Question

 select nl.ndc, formulary_status FROM bh.webdav_formulary_detail wfd 
 INNER JOIN bh.payer_map pm ON wfd.payer_map_id = pm.payer_map_id 
 INNER JOIN bh.ndc_lookup nl ON wfd.product_id = nl.uid 
 WHERE pm.payer_id ='P00000000001001' 
   and pm.formulary_id='01244' 
   and nl.ndc in ('16590061572' , '35356078830' , '35356078860' , '35356078890' , 
                  '49999085690' , '54868381500' , '54868381501' , '54868381503' , 
                  '54868381504' , '54868381505' , '59011044010' , '59011044020' , 
                  '63629377401' , '63629377402' , '63629377403');

The below mysql tables is myisam engine

SHOW CREATE TABLE webdav_formulary_detail;

CREATE TABLE webdav_formulary_detail (
  product_id mediumint(8) unsigned NOT NULL,
  formulary_status char(2) NOT NULL,
  file_iid smallint(5) unsigned NOT NULL DEFAULT '0',
  payer_map_id smallint(5) unsigned NOT NULL,
  KEY payer_map_id (payer_map_id),
  KEY product_id (product_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE ndc_lookup (
  uid mediumint(8) unsigned NOT NULL,
  ndc char(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (uid),
  KEY uid (uid),
  KEY ndc (ndc)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE payer_map (
  payer_map_id smallint(5) unsigned NOT NULL,
  payer_id varchar(80) DEFAULT NULL,
  formulary_id varchar(50) DEFAULT NULL,
  alternate_id varchar(50) DEFAULT NULL,
  PRIMARY KEY (payer_map_id),
  KEY payer_map_id (payer_map_id),
  KEY payer_id (payer_id),
  KEY formulary_id (formulary_id),
  KEY alternate_id (alternate_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 

How i can optimize the above mysql query to improve its execution time to less than half minute ?

Answer 1

WHERE pm.payer_id ='P00000000001001' 
  and pm.formulary_id='01244' 

begs for a composite index on payer_map: INDEX(payer_id, formulary_id) (or the opposite order). Slightly better would be to switch to InnoDB or to create a 'covering' index: INDEX(payer_id, formulary_id, payer_map_id).

1. The optimizer is likely to start with the table with the best filtering possibilities -- pm. The INDEX I gave you will make that efficient.
2. The next table must be wfd, using payer_map_id. wfd has a good index for that.
3. Finally nl will come in using uid. Again there is a pretty good index. Actually, INDEX(uid, ndc) would be slightly better because of being a "covering" index. Or switching to InnoDB would be good because of the 'clustering' of the PRIMARY KEY.

Always provide EXPLAIN SELECT...; to see what the optimizer is doing.

Unrelated to optimization:

PRIMARY KEY      (payer_map_id),
KEY payer_map_id (payer_map_id),

A PRIMARY KEY is a UNIQUE KEY is a KEY, hence the latter is totally redundant; DROP it. Ditto for uid.

My cookbook has more on how to create indexes from a SELECT.