MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:December 21 2005 11:53am
Subject:Re: date index question
View as plain text  
Hello.

Add composite index (tValidFrom, tValidTo) and use constant or variable
instead of now(). Force MySQL to use this composite index.


> Hello People,
> 
> I have some indexing problem on using the fieldtype 'date' as
> restriction in a query.
> I use MySQL Server version: 4.0.20
> 
> The table I have consists of roughly over 200.000 rows about 37 fields and
> it looks sort of like this:
> 
> mysql> desc the_table;
>
> +----------------+--------------+------+-----+---------------------+----------------+
> 
> | Field          | Type         | Null | Key | Default             |
> Extra          |
>
> +----------------+--------------+------+-----+---------------------+----------------+
> 
> | theID                   | int(11)      |      | PRI |
> NULL                | auto_increment |
> | contract       | varchar(20)  |      | MUL |                    
> |                |
> ... some varchars ...
> | rate1              | double(10,2) |      |     | 0.00               
> |                |
> ... twenty other doubles ...
> | routing        | varchar(100) |      |     |                    
> |                |
> | cNotes         | text         |      |     |                    
> |                |
> | tValidfrom     | date         |      | MUL | 0000-00-00         
> |                |
> | tValidto       | date         |      | MUL | 0000-00-00         
> |                |
> | iStatus        | int(11)      |      |     | 0                  
> |                |
> | iEnteredby     | int(11)      |      |     | 0                  
> |                |
> | tEntered       | datetime     |      |     | 0000-00-00 00:00:00
> |                |
> | iUpdatedby     | int(11)      |      |     | 0                  
> |                |
> | tUpdated       | datetime     |      |     | 0000-00-00 00:00:00
> |                |
>
> +----------------+--------------+------+-----+---------------------+----------------+
> 
> 37 rows in set (0.00 sec)
> 
> I then run this to create the index I want.
> 
> mysql> CREATE INDEX IX_test ON the_table(tValidfrom,tValidto);
> Query OK, 204657 rows affected (1 min 6.08 sec)
> Records: 204657  Duplicates: 0  Warnings: 0
> 
> The created index shows this info:
> 
>       Table: the_table
>  Non_unique: 1
>    Key_name: IX_test
> Seq_in_index: 1
> Column_name: tValidfrom
>   Collation: A
> Cardinality: 75
>    Sub_part: NULL
>      Packed: NULL
>        Null:
>  Index_type: BTREE
>     Comment:
> 
>       Table: the_table
>  Non_unique: 1
>    Key_name: IX_test
> Seq_in_index: 2
> Column_name: tValidto
>   Collation: A
> Cardinality: 131
>    Sub_part: NULL
>      Packed: NULL
>        Null:
>  Index_type: BTREE
>     Comment:
> 27 rows in set (0.00 sec)
> 
> So now I want to take advantage of this index, but my query is still slow:
> 
> mysql> SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom
> <= now() AND the_table.tValidTo >= now();
> 
> ....
> ....
> | USBDA   |
> | USIND   |
> | USHSV   |
> +---------+
> 402 rows in set (2.80 sec)
> 
> Then I do an explain to try to find out if it is using my index.
> 
> mysql> explain SELECT DISTINCT cOrigin from the_table WHERE
> the_table.tValidFrom <= now() AND the_table.tValidTo >= now() \G;
> *************************** 1. row ***************************
>        table: the_table
>         type: ALL
> possible_keys: IX_test
>          key: NULL
>      key_len: NULL
>          ref: NULL
>         rows: 204657
>        Extra: Using where; Using temporary
> 1 row in set (0.00 sec)
> 
> So it's not even using my index =(
> 
> And if I force it:
> 
> mysql> SELECT DISTINCT cOrigin from the_table FORCE INDEX (IX_test)
> WHERE the_table.tValidFrom <= now() AND the_table.tValidTo >= now();
> .....
> .....
> | IDPJG   |
> | JPSHI   |
> | INICD   |
> | CNJIU   |
> | USHSV   |
> +---------+
> 402 rows in set (4.27 sec)
> 
> It is even slower. I have done a check table, analyze table etc.
> If you have any suggestions please let me know, thanks for your precious
> time!
> 
> Best Regards,
> Mattias
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



Thread
date index questionMattias HÃ¥kansson20 Dec
  • Re: date index questionGleb Paharenko21 Dec