Heikki, Here is what you asked for:
mysql> EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE datestamp <= 20020213185230; +--------+------+---------------+------+---------+------+------+-------- ----+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------+------+---------------+------+---------+------+------+-------- ----+ | Syslog | ALL | ds_index | NULL | NULL | NULL | 4719 | where used | +--------+------+---------------+------+---------+------+------+-------- ----+ 1 row in set (0.00 sec) And here is a single InnoDB Monitor output: ===================================== 020214 9:07:25 INNODB MONITOR OUTPUT ===================================== ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 85376, signal count 83573 Mutex spin waits 107640, rounds 1033422, OS waits 33331 RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits 34400 ------------ TRANSACTIONS ------------ Trx id counter 0 10816708 Purge done for trx's n:o < 0 475530 undo n:o < 0 0 Total number of lock structs in row lock hash table 13009 ---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or sleeps MySQL thread id 205, query id 12109132 localhost root INSERT INTO logs.Syslog set caid='630', datestamp=NULL, message='4187481481 1481418748 418748 418748 ---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting, active, runs or sleeps, has 13010 lock struct(s), undo log entries 650408 MySQL thread id 344, query id 6056015 localhost root updating DELETE FROM logs.Syslog where (datestamp <= (20020213190141 + (3600))) -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 1 1164459 OS file reads, 1266570 OS file writes, 100823 OS fsyncs 23.25 reads/s, 27.88 writes/s, 2.06 fsyncs/s ------------- INSERT BUFFER ------------- Ibuf for space 0: size 518, free list len 272, seg size 791, 9152117 inserts, 8984010 merged recs, 675487 merges --- LOG --- Log sequence number 0 4190770295 Log flushed up to 0 4190657162 Last checkpoint at 0 4178363488 1 pending log writes, 0 pending chkp writes 52481 log i/o's done, 0.88 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 33029216; in additional pool allocated 269312 Free list length 121 LRU list length 856 Flush list length 805 Buffer pool size 1024 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1256311, created 255856, written 1602244 24.31 reads/s, 3.88 creates/s, 36.00 writes/s Buffer pool hit rate 994 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB; main thread: flushing log Number of rows inserted 10806106, updated 0, deleted 806508, read 6850315 181.44 inserts/s, 0.00 updates/s, 11.69 deletes/s, 11.69 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ Eric > -----Original Message----- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 13, 2002 5:59 PM > To: Eric Mayers; [EMAIL PROTECTED] > Subject: Re: Does delete from .. where (condition) use an index? > > > 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
