MySQL query optimization for legacy application

myisamMySQLperformancequery-performance

I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone.

The fact that they have actual data now years later is why we're noticing the performance hit at this time. Just not sure what to do with it yet.

I've tried to make sure indexes are correct and what not in the schema, but here's a sample query being generated by the application. Any ideas to help with performance?

SELECT id, 
       name, 
       phone 
FROM   customers 
WHERE  ( last_updated >= '2013-03-01' 
         AND last_updated <= '2013-03-27 24:00:00' 
         AND status = '0' 
         AND deadlead = '' 
         AND purchased = '' 
         AND id != 0 ) 
        OR ( status = '0' 
             AND deadlead = '' 
             AND purchased = '' 
             AND id IN (SELECT DISTINCT cid 
                        FROM   notes 
                        WHERE  timestamp >= '2013-03-01' 
                               AND timestamp <= '2013-03-27 24:00:00') 
             AND id != 0 ) 

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `alt_phone` varchar(255) NOT NULL,
  `tradein` text NOT NULL,
  `purchased` text NOT NULL,
  `deadlead` text NOT NULL,
  `interest` varchar(255) NOT NULL,
  `trailer` varchar(255) NOT NULL,
  `status` binary(1) NOT NULL,
  `options` text NOT NULL,
  `created_on` date NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `id_2` (`id`),
  KEY `id` (`id`),
  KEY `id_3` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8913 ;

CREATE TABLE IF NOT EXISTS `notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `note` text NOT NULL,
  `file` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `reminder` date NOT NULL,
  `locked` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19308 ;

UPDATED:

This is the output of using EXPLAIN on the query:

EXPLAIN statement

Best Answer

I will add to the recommendations made by @RolandoMySQLDBA.

It looks like notes.cid should be a foreign key related to customers.id.

You might try replacing the SELECT DISTINCT sub query with an EXISTS sub query. The select distinct will look for all related notes. An exists may perform better since it only check for the existence of at least one note.

-- Current:
AND id IN (SELECT DISTINCT cid 
                        FROM   notes 
                        WHERE  timestamp >= '2013-03-01' 
                               AND timestamp <= '2013-03-27 24:00:00') 




-- suggested replacement:
AND EXISTS (
    SELECT *
    FROM notes
    WHERE notes.cid = customers.id
        AND  notes.timestamp >= '2013-03-01' 
        AND notes.timestamp <= '2013-03-27 24:00:00'
)
Related Question