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
