Hi Mikhail,
I may not have been precise in my question, but the Unique Index in
question is a two fields index, and I was looking to find out wisdom
from the List if there is sense and/or experience in keying second(left)
field on in the Unique Index to speed up a search.
If you have a UNIQUE(a, b), then MySQL can use it as an index for (a),
or (a, b), but NOT for (b). In this context, it won't help generally to
create an index on (a), but it may help to create one on (b) depending
on your queries.
I am dealing with 32M rows table, where second field in the Unique Index
is a date field. Unfortunately for my simple "SELECT MAX(Date) as
Latest_Date from THE_TABLE" took 4 minutes and some seconds, so before I
will go and buy bigger server I needed to re-assure myself that there is
no other way.
Four minutes to find a MAX(date) is too long for any kind of hardware.
It should be much faster. Can you post the output of:
* SHOW CREATE TABLE tbl
* SHOW VARIABLES
FYI:
mysql> select max(dep_time) from ontime_all;
+---------------------+
| max(dep_time) |
+---------------------+
| 2005-05-31 23:59:00 |
+---------------------+
1 row in set (49.76 sec)
mysql> select count(*) from ontime_all;
+----------+
| count(*) |
+----------+
| 33395077 |
+----------+
1 row in set (0.00 sec)
Could be a lot faster, even, but these are MERGE tables so it's really
65 tables that are being checked...
Regards,
Jeremy
--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
