Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
I see no indexes except for the primary keys. And table customers has the same index 3 times! –  ypercube Mar 28 at 19:14
    
Please post the explain plan. See: dev.mysql.com/doc/refman/5.5/en/using-explain.html –  Jeremy Mar 28 at 19:18
    
Updated question with EXPLAIN. Thanks for the tip! –  Will Ashworth Mar 28 at 19:47

2 Answers 2

up vote 1 down vote accepted

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'
)
share|improve this answer
    
Honest...if I could pick two correct answers, I would. Because you both helped and both of your solutions had a hand in the solution, but Jeremy did great with a knockout solution at the query level. Thank you! –  Will Ashworth Mar 28 at 21:43

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 !!!

share|improve this answer
    
This helped, but I have to admit... @Jeremy had a solution that annihilated this performance issue. Advice from RolandoMySQLDBA got this from 32 seconds to run down to about 14 seconds. However, the solution provided by Jeremy helped me get the time to load the page down to 0.0123 seconds when it was over 32 seconds before. That’s 2,600 times faster!! –  Will Ashworth Mar 28 at 21:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.