Hi Jigal
Thanks a lot for your answer. Sorry for the confusion about DELETE and
SELECT.
What we are trying to optimize are some DELETE statements, it was just
that while investigating we found this behaviour of Mysql not using some
date indexes if we change from using "=" operator to use >= or <=
Please find below all the details.
Mysql version: 4.1.12
Operating System: Linux
Table Type: InnoDB
Number of records in table: More than 10 million
process_times CREATE TABLE `process_times` (
`ID` bigint(20) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`subagent` char(3) NOT NULL default '',
`client_id` varchar(128) NOT NULL default '',
`status` int(11) NOT NULL default '0',
`process_time` double NOT NULL default '0',
`host` varchar(20) NOT NULL default '',
`process` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `client_idx` (`client_id`),
KEY `status_idx` (`status`),
KEY `subag_idx` (`subagent`),
KEY `mias_idx2` (`host`),
KEY `date_idx` (`date`),
KEY `process` (`process`),
KEY `date_proc_idx` (`date`,`process`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DELETE queries we want to optimize:
DELETE FROM process_times
WHERE (date <= date_sub(now(), INTERVAL VariableX VariableY)) AND
(process=VariableZ)
While investigating we tried different SELECT statement to check is
Mysql was using the Index: date_proc_idx
See below what we found
1- Select using "<=" operator. As you will see in the explain response
not index is being used
EXPLAIN SELECT * FROM process_times
WHERE date <= date_sub(now(), INTERVAL 2 day)
Explain results:
<data>
<row>
<id>1</id>
<select_type>SIMPLE</select_type>
<table>process_times</table>
<type>ALL</type>
<possible_keys>date_idx,date_proc_idx</possible_keys>
<key>(NULL)</key>
<key_len>(NULL)</key_len>
<ref>(NULL)</ref>
<rows>10778561</rows>
<Extra>Using where</Extra>
</row>
</data>
2- Select using "=" operator. date_proc_idx index is used
EXPLAIN SELECT * FROM process_times
WHERE date = date_sub(now(), INTERVAL 2 day)
Explain results:
<data>
<row>
<id>1</id>
<select_type>SIMPLE</select_type>
<table>process_times</table>
<type>ref</type>
<possible_keys>date_idx,date_proc_idx</possible_keys>
<key>date_idx</key>
<key_len>3</key_len>
<ref>const</ref>
<rows>1863456</rows>
<Extra>Using where</Extra>
</row>
</data>
3- Adding condition for "process" field.
EXPLAIN SELECT * FROM process_times
WHERE date = date_sub(now(), INTERVAL 2 day) and process=1
Explain results:
- <data>
- <row>
<id>1</id>
<select_type>SIMPLE</select_type>
<table>process_times</table>
<type>ref</type>
<possible_keys>date_idx,process,date_proc_idx</possible_keys>
<key>date_proc_idx</key>
<key_len>7</key_len>
<ref>const,const</ref>
<rows>550726</rows>
<Extra>Using where</Extra>
</row>
</data>
4 - Again with condition for "process" field and changing operator from
"=" to "<=". This cause Mysql to use a diffent index, in this case the
index for process
EXPLAIN SELECT * FROM process_times
WHERE date <= date_sub(now(), INTERVAL 2 day) and process=1
Explain results:
- <data>
- <row>
<id>1</id>
<select_type>SIMPLE</select_type>
<table>process_times</table>
<type>ref</type>
<possible_keys>date_idx,process,date_proc_idx</possible_keys>
<key>process</key>
<key_len>4</key_len>
<ref>const</ref>
<rows>1830334</rows>
<Extra>Using where</Extra>
</row>
</data>
After seeing this we are now worry about the fact maybe many queries we
have based on date fields are not using the indexes in the way we were
expecting.
I would like also ask if is valid to expect that the results of the
Explain statement for a query like this
SELECT * FROM TABLE-X WHERE [CONDITIONS]
Are valid for the equivalent
DELETE FROM TABLE-X WHERE [CONDITIONS]
Thanks a lot for your help
Javier
-----Original Message-----
From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
Sent: 05 January 2006 14:39
To: Javier Diaz
Cc: Aftab Khan; [email protected]
Subject: Re: Problems with indexes on Date/DateTime fields
Javier Diaz wrote:
> I would like to know if there is any problem which cause Mysql to not
> use date indexes at least you use the "=" operator, because if that is
> the case we will need to re-visit a few queries ....
If you do a select instead of a delete, will the index be used? (You can
check this by using EXPLAIN SELECT....)
If the index is used in that case MySQL must have a reason for not using
the index for deleting a range. With MyISAM tables deleting a single
date involves a single leave in the index tree, deleting multiple dates
requires MySQL to merge index leaves during the delete. You could use
DELETE QUICK to suppress the merging of index blocks, but you need to do
an OPTIMIZE later on to reclaim the unused index space. The query
optimizer might decide that using the index in this case is slower than
a full table scan.
If the index is not used with the select it might be because the index
makes the optimizer think that more than approx. 30% of the records will
be involved. In such a case it is usually faster to directly access the
data than to use an index (which would require access to the index plus
index to the data).
It will be more likely for you to get relevant answers from this list if
you supply the definition of the table(s), indexes, engine, etc. (a
CREATE TABLE statement is very good for this purpose); the exact query
which you use (and nog give a SELECT and later on introduce the fact
that it was actually a DELETE query ;-) ); in case of a select the
output from EXPLAIN SELECT...
With this information the gurus here (and that does not include me :-) )
can more easily judge the situation and tell you what might be the cause
of your problem.
Regards, Jigal.
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
