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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
