Need help with optimization of queries

[sameerpanjwani]

My queries have been taking really really long to load these last few weeks, and I'm guessing it could be because of more users and increased table size. Anyway the table that's involved is as follows:

CREATE TABLE IF NOT EXISTS `dsitesubmissions` (
  `submit_id` int(11) NOT NULL auto_increment,
  `siteid` int(11) NOT NULL default '0',
  `did` int(11) NOT NULL default '0',
  `transactionid` int(11) NOT NULL default '0',
  `registration_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `submission_date` datetime default NULL,
  `approved` enum('Unknown','Rejected','Approved') default 'Unknown',
  `link_location` varchar(255) default NULL,
  `tdid` int(11) default NULL,
  `payment_status` enum('Paid','Unpaid','Previous Submission','Custom') NOT NULL default 'Unpaid',
  `operator_id` int(10) NOT NULL default '0',
  `time_taken` decimal(10,2) NOT NULL default '0.00',
  `trylater` int(11) NOT NULL default '0',
  `skipped` int(11) NOT NULL default '0',
  `url_error` int(11) NOT NULL default '0',
  `link_urls` varchar(255) NOT NULL default 'none',
  `link_text` varchar(255) NOT NULL default 'none',
  `category_text` varchar(255) NOT NULL default 'none',
  `category_index` int(10) NOT NULL default '0',
  PRIMARY KEY  (`submit_id`),
  UNIQUE KEY `sd` (`siteid`,`did`),
  KEY `registration_date` (`registration_date`),
  KEY `submission_date` (`submission_date`),
  KEY `transactionid` (`transactionid`),
  KEY `siteid` (`siteid`),
  KEY `did` (`did`),
  KEY `tdid` (`tdid`)
) ENGINE=MyISAM 
 

It has 4.5 million records and the query that's been taking long is shown below:

SELECT submit_id
FROM dsitesubmissions AS a, dsitedetails c
WHERE (submission_date IS NULL OR submission_date='0000-00-00 00:00:00'
)
AND payment_status = 'Paid'
AND c.status = 'Active'
AND DATE_FORMAT( a.registration_date, '%Y-%m-%d' ) <= CURDATE( )
AND c.siteid = a.siteid
AND selection_needed = 'No'
 

When I do an explain it shows:

select_type     table     type     possible_keys     key     ref     rows     Extra
 
SIMPLE     a     ref_or_null     sd,submission_date,siteid     submission_date     const     67587     Using where
 
SIMPLE     c     eq_ref     PRIMARY     PRIMARY     inksmax_main.a.siteid     1     Using where
 

While I think 67000 is a relatively small number of rows out of the 4.6 million rows, I dont know why the query's been taking so long...could it be a problem that there are inserts/updates also taking place on the same table every few seconds concurrently while the selects are being run? If so, that's a situation I cannot avoid.

I need a solution fast.

[mwasif]

Change

DATE_FORMAT( a.registration_date, '%Y-%m-%d' ) <= CURDATE( )

to

a.registration_date <= NOW( )

Do you have index on c.siteid? If it is not, then create an index on it.

Now do an EXPLAIN and compare the results.