Eric, MySQL is very pessimistic about key accesses: it assumes many of them cause a random disk read.
Please print EXPLAIN SELECT * FROM Syslog WHERE datestamp < ... to see how it accesses the table. I have tuned the optimization in 3.23.48 so that it would favor index searches more often. Please try also with 3.23.48. Regards, Heikki Innobase Oy "Eric Mayers" wrote in message ... >I haven't been able to find details about how/if MySQL (InnoDB) >optimizes deletes. Does it use indexes? Can I force it to use an >index? > >My table is defined as: > >CREATE TABLE Syslog( > id int(11) not null, > datestamp timestamp(14), > message char(255), > KEY ds_index(datestamp), > KEY id_index(id) >) type=InnoDB; > > >There are about 15 mil rows, and I'm just trying to delete a small >portion of them (0.05% perhaps). My delete looks like: > >DELETE FROM Syslog WHERE datestamp <= 2002021310712; > >(the magic number there comes from "SELECT (min(datestamp) + 30) AS min >FROM Syslog" in a previous query). > >.. and its taking a very long time.. watching the innodb monitor I'm >seeing a large number of reads/s (29000) and a small number of deletes/s >(10) which makes me believe its not using the index.. > >Any way to improve this? > >Eric Mayers >Software Engineer I --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
