Hi,
I am using MySQL 5.0.24. My indexing does not seem to be working.
I have a table of about 300,000 rows and a query that supposedly uses "Using where; using Index" is still taking 1.3 seconds and times like that! The database is running on SCSI disks with 2 GB of memory!
My SQL statments are something like these:
1. SELECT col1 FROM table1 WHERE col1 = x AND col2 = y AND col3 = z
2. SELECT col1 FROM table1 WHERE col3 = z
3. SELECT col1 FROM table1 WHERE col2 = y
4. SELECT col1 FROM table1 WHERE col1 = x AND col3 = z
5. SELECT col1 FROM table1 WHERE col1 = x AND col2 = y AND col4 = z
I have set the index as (col1, col2, col3, col4)
From my understanding, all of the above statements should be covered by this index? WHat am I missing?
Here is an actual EXPLAIN statement and then the execution of the SQL. I have replaced the names of columns and the table for privacy, but the numbers are as-is.
mysql> explain select count(*) from table1 where col1 = 1 and col2 = 'X' ;
+----+-------------+---------+------+-------------------------------------------------------+---------------+---------+-------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-------------------------------------------------------+---------------+---------+-------------+--------+--------------------------+
| 1 | SIMPLE | table1 | ref | col1_2,col1,col1_3,col1_4 | col1_3 | 10 | const,const | 219707 | Using where; Using index |
+----+-------------+---------+------+-------------------------------------------------------+---------------+---------+-------------+--------+--------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from table1 where col1 = 1 and col2 = 'X' ;
+----------+
| count(*) |
+----------+
| 248348 |
+----------+
1 row in set (0.32 sec)
I think 0.32 seconds on something that is using an index on my kind of hardware is unacceptable! In other databases, I see response times like 0.001 seconds on tables with 1.5 million rows! What could be wrong? What kinds of things can I look at?
Many thanks
.ep