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.
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.
Comment