updating DATETIME index performance problems
I have a table of about 120,000 rows that contain auctions data.
------
CREATE TABLE `auctions` (
`id` int(11) NOT NULL auto_increment,
`seller` int(11) NOT NULL default '0',
`category` int(11) NOT NULL default '0',
`title` varchar(75) NOT NULL default '',
`description` text NOT NULL,
`duration` int(3) NOT NULL default '0',
`closed` enum('0','1') NOT NULL default '0',
`starts` datetime NOT NULL default '0000-00-00 00:00:00',
`ends` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `seller` (`seller`),
KEY `starts` (`starts`),
KEY `ends` (`ends`),
KEY `category` (`category`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
------
I believe all the keys are necessary, as the table is constantly selected based on the start / ends fields, and also by seller id and category id.
There is naturally a large variation in the `ends` values as auctions have finished at various times 24/7 over a couple of years. Unless sold, generally the item will relist.
This basically is set with the following ($now is a PHP variable):
UPDATE auctions SET starts = '$now', ends = DATE_ADD(ends, INTERVAL `duration` DAY) WHERE ends < '$now' AND closed = '0'
I have tried updating this within a PHP loop row by row, as well as the multi row query as above. To my surprise, the multi row statement only provides a marginal performance improvement.
On my test machine, updating the entire table takes 70-80 seconds, which for accentuated testing purposes affects about 49,000 rows.
Now here is the problem - when this is running, all other queries become LOCKED, which I believe is the result of the index being updated. Of course, in a production environment this produces unacceptable lag.
I thought that if I ran the multi-row statement, the index would update only once and very quickly, whereas with the row by row method it would have to update on every row.
So it seems like a rock and a hard place - my SELECT is too slow without the starts/ends index (probably 20% slower), and UPDATES are too slow WITH the index (about 3-4 times slower).
What am I doing wrong here?