Eric, print what
EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE ... says. Please also show what the InnoDB monitor prints. Regards, Heikki -----Original Message----- From: Eric Mayers <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]>; [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, February 14, 2002 3:44 AM Subject: RE: Does delete from .. where (condition) use an index? >Heikki, > >mysql> EXPLAIN SELECT * FROM Syslog WHERE datestamp <= 2002021310712; >+--------+------+---------------+------+---------+------+---------+----- >-------+ >| table | type | possible_keys | key | key_len | ref | rows | >Extra | >+--------+------+---------------+------+---------+------+---------+----- >-------+ >| Syslog | ALL | ds_index | NULL | NULL | NULL | 2204932 | >where used | >+--------+------+---------------+------+---------+------+---------+----- >-------+ >1 row in set (0.00 sec) > >How can I force it to use the index? > >(the indexes look like: > >mysql> show index from Syslog; >+--------+------------+----------+--------------+-------------+--------- >--+-------------+----------+--------+---------+ >| Table | Non_unique | Key_name | Seq_in_index | Column_name | >Collation | Cardinality | Sub_part | Packed | Comment | >+--------+------------+----------+--------------+-------------+--------- >--+-------------+----------+--------+---------+ >| Syslog | 1 | ds_index | 1 | datestamp | A >| 5893 | NULL | NULL | | >| Syslog | 1 | ca_index | 1 | caid | A >| 9730 | NULL | NULL | | >+--------+------------+----------+--------------+-------------+--------- >--+-------------+----------+--------+---------+ >2 rows in set (6.90 sec) > >) > >Thanks, >Eric > > >> -----Original Message----- >> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] >> Sent: Wednesday, February 13, 2002 5:34 PM >> To: [EMAIL PROTECTED] >> Subject: Re: Does delete from .. where (condition) use an index? >> >> >> 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 >> >> --------------------------------------------------------------------- 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
