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.