Hello.
Add composite index (tValidFrom, tValidTo) and use constant or variable instead of now(). Force MySQL to use this composite index. Mattias Håkansson wrote: > 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
