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.
smime.p7s
Description: S/MIME Cryptographic Signature
