MySQL query optimization for legacy application 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 &gt;= '2013-03-01' 
         AND last_updated &lt;= '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 &gt;= '2013-03-01' 
                               AND timestamp &lt;= '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:
SUGGESTION #1

You have no meaningful indexes. That can have a detrimental effect on execution since the MySQL Query Optimizer has no help in searching.

You should all the following indexes

ALTER TABLE customers ADD INDEX bigindex (status,deadlead,purchased,last_updated);
ALTER TABLE notes ADD INDEX timestamp_id_index (timestamp,id);


This may help with some of the searching

SUGGESTION #2

You have duplicate indexes. That can slow down INSERTs and bloat the MYI file.

Please run 

ALTER TABLE customers DROP INDEX id,DROP INDEX id3;


SUGGESTION #3

Periodically, perhaps once a week, you should update index statistics

ANALYZE TABLE customers;
ANALYZE TABLE notes;


CAVEAT

There could be other things I could suggest. However, if the amount of data is relatively small, indexing is probably all you need.

Give it a Try !!!