calebb
[H]F Junkie
- Joined
- Mar 26, 2000
- Messages
- 8,758
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!
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!