MySQL Multi-part Index Question

[calebb]

Hi,

I'm a long-time lurker, first time poster (in this forum)....

(haha, ok, just kidding, but I've always wanted to say that)

Anyway, I'm working on a MySQL database that I did not design. Whoever designed it made some interesting choices.

There are many fields, but I will highlight a few:

JULIAN_DAY - Primary Key - not really the Julian Day, it's actually just the date in the format yyyy-mm-dd
HR_MIN - Primary Key - This is the time stamp of the entry in the format hh:mm:ss
date_time - Not a primary key - just a simple contatenation of JULIAN_DAY HR_MIN with a space between them.

Now there is a PHP script that queries data from the database and it is INCREDIBLY slow. The reason for the speed problem is that it performs this query:

SELECT JULIAN_DAY, UNIT_ID, HR_MIN, TOT_SOLAR, AV_AIR_TMP, REL_HUMID, AV_DEWPT, AV_WND_SP, AV_WND_DIR, DV_WND_DIR, TOT_PRECIP, AV_LEAF_WT, AV_SL_MOIS, AV_SL_TMP8, MIN_AIRTMP FROM MIN_821 WHERE date_time BETWEEN '2005-7-24 12:15:00' AND '2005-7-25 12:15:59';

Note the bold section... it's not using the primary key to limit the query.

So, what I need to do is modify the code so that it uses the primary key correctly. (I'm not able to alter the database so that date_time is the primary key).

I read through the mysql.com docs on indexes
here (general index help) and here (multi-column index help) but I am still unable to write a functioning query that uses both indexes (indices?).

According to the docs, it should be something like (key1,key2), ('value1', 'value2'), etc.

I've tried many variations with no success... has anyone else had any luck with range queries on multi-column indexes?

Here are some samples of what I tried...

SELECT JULIAN_DAY, UNIT_ID, HR_MIN, TOT_SOLAR, AV_AIR_TMP, REL_HUMID, AV_DEWPT, AV_WND_SP, AV_WND_DIR, DV_WND_DIR, TOT_PRECIP, AV_LEAF_WT, AV_SL_MOIS, AV_SL_TMP8, MIN_AIRTMP FROM MIN_662 WHERE ("2005-7-24","12:15:00") < (JULIAN_DAY,HR_MIN) < ("2005-7-25","12:15:59");

SELECT JULIAN_DAY, UNIT_ID, HR_MIN, TOT_SOLAR, AV_AIR_TMP, REL_HUMID, AV_DEWPT, AV_WND_SP, AV_WND_DIR, DV_WND_DIR, TOT_PRECIP, AV_LEAF_WT, AV_SL_MOIS, AV_SL_TMP8, MIN_AIRTMP FROM MIN_662 WHERE ("2005-7-25","12:15:59") > (JULIAN_DAY,HR_MIN) > ("2005-7-24","12:15:00");

SELECT JULIAN_DAY, UNIT_ID, HR_MIN, TOT_SOLAR, AV_AIR_TMP, REL_HUMID, AV_DEWPT, AV_WND_SP, AV_WND_DIR, DV_WND_DIR, TOT_PRECIP, AV_LEAF_WT, AV_SL_MOIS, AV_SL_TMP8, MIN_AIRTMP FROM MIN_662 WHERE ((JULIAN_DAY, HR_MIN) BETWEEN ('2005-7-24,12:15:00') AND ('2005-7-25,12:15:59');

Thanks in advance!

 

[calebb]

Just a little more information...

Here's the original query...

EXPLAIN SELECT JULIAN_DAY, UNIT_ID, HR_MIN, TOT_SOLAR, AV_AIR_TMP, REL_HUMID, AV_DEWPT, AV_WND_SP, AV_WND_DIR, DV_WND_DIR, TOT_PRECIP, AV_LEAF_WT, AV_SL_MOIS, AV_SL_TMP8, MIN_AIRTMP FROM MIN_821 WHERE date_time BETWEEN '2005-7-24 12:15:00' AND '2005-7-25 12:15:59'

552267 rows (examined) - no wonder it's so slow!! There are about 100 of these tables and each is about .5 mil rows.

EXPLAIN SELECT JULIAN_DAY, UNIT_ID, HR_MIN, TOT_SOLAR, AV_AIR_TMP, REL_HUMID, AV_DEWPT, AV_WND_SP, AV_WND_DIR, DV_WND_DIR, TOT_PRECIP, AV_LEAF_WT, AV_SL_MOIS, AV_SL_TMP8, MIN_AIRTMP FROM MIN_662 WHERE (JULIAN_DAY) between '2005-7-24' AND '2005-7-25' AND HR_MIN BETWEEN ('2005-7-24,12:15:00') AND ('2005-7-25,12:15:59');

This query sort-of works...
55 rows examined - but those are just the rows that match the JULIAN_DAY query I think. This query only returns 2 rows - the 12:15 data on 7/24 and the 12:15 data on 7/25. (It should return more like 96 rows - 24 hours of data x 15 minute interval)

Maybe I need to find a MySQL forum

 

[calebb]

Ok, I solved the problem using a method that I said I couldn't do... I just created a new index on the date_time field.

It looks like multi-column indexes aren't as useful as the DB designer initially thought. You can do a search on Key1 or Key1,Key2 - but not Key2 alone.

Anyway, maybe this thread will help someone someday

Caleb