List:General Discussion« Previous MessageNext Message »
From:Jeremy Cole Date:November 17 2005 10:22pm
Subject:Re: A "key" question
View as plain text  
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
Thread
A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJasper Bryant-Greene17 Nov
  • Re: A "key" questionPeter Brawley17 Nov
RE: A "key" questionMikhail Berman17 Nov
  • Re: A "key" questionJeremy Cole17 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionJeremy Cole18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionMichael Stassen18 Nov
RE: A "key" questionMikhail Berman18 Nov
  • Re: A "key" questionDavid Griffiths18 Nov
  • Connection ProblemTripp Bishop18 Nov
    • Re: Connection ProblemMichael Stassen18 Nov
      • Re: Connection ProblemTripp Bishop18 Nov