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:
[code=mysql]
CREATE TABLE IF NOT EXISTS `dsitesubmissio ns` (
`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_d ate` datetime NOT NULL default '0000-00-00 00:00:00',
`submission_dat e` datetime default NULL,
`approved` enum('Unknown', 'Rejected','App roved') default 'Unknown',
`link_location` varchar(255) default NULL,
`tdid` int(11) default NULL,
`payment_status ` enum('Paid','Un paid','Previous Submission','Cu stom') 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_d ate` (`registration_ date`),
KEY `submission_dat e` (`submission_da te`),
KEY `transactionid` (`transactionid `),
KEY `siteid` (`siteid`),
KEY `did` (`did`),
KEY `tdid` (`tdid`)
) ENGINE=MyISAM
[/code]
It has 4.5 million records and the query that's been taking long is shown below:
[code=mysql]
SELECT submit_id
FROM dsitesubmission s AS a, dsitedetails c
WHERE (submission_dat e 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_neede d = 'No'
[/code]
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
[CODE=mysql]DATE_FORMAT( a.registration_ date, '%Y-%m-%d' ) <= CURDATE( )[/CODE]
to
[CODE=mysql]a.registration_ date <= NOW( )[/CODE]
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.