Hi! I tested with .48 and it seems that if the estimator thinks you are going to delete > 10 % of the rows in your table, it scans the whole table.
In .47 this % limit is smaller, because I changed .48 to favor more index searches over table scans. But I tested with a very uniform table. Your table is less uniform and the estimator may be less accurate. Make sure you commit your transactions often, because only then purge can remove delete marked records from indexes. If purge cannot run, you may have > 10 % of records delete marked but not yet removed at the old end of the index. That will mislead the estimator. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -----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
