Hello,
I'm working on using a temporary table to get the group-wise maximum for
a number of items. I have a table structure like:
CREATE TABLE item (
item_id int not null primary key,
name varchar(100) not null);
CREATE TABLE attribute_a (
index (item_id, time_id),
item_id int not null,
time_id datetime not null,
value int not null);
Each item has many attributes. The time_id of an attribute contains the
time of the /last /update to the attribute. My objective is to return
the the attribute's value for each item at a requested time. I believe
the best solution is to get the maximum time_id's (<= requested time)
and dump them into a temporary table. Then use these times to retrieve
the actual values.
My problem is that as I add a number of attributes the query time bogs
down when creating the temporary table. The query looks something like:
CREATE TEMPORARY TABLE times
(INDEX (item_id))
SELECT item.item_id,
MAX(attribute_a.time_id) AS attribute_a_time
FROM item
LEFT JOIN attribute_a ON
(attribute_a.item_id = item.item_id
AND attribute_a.time_id < '2000-10-01 00:00')
GROUP BY item.item_id;
I add a LEFT JOIN for each attribute that I want to retrieve (up to 60).
Let's say each attribute table contains 10,000 rows (all with dates less
than '2000-10-01'). If I understand things correctly, using this query
10,000 rows would be added for each attribute that I joined. If I added
60 attributes MySQL would have to handle grouping 600,000 rows! Is that
correct? Is there a better solution?
I tried using sub queries but that didn't seem to be much faster. If I
had 1,000 items to search for 60 attributes the sub queries would be
called 60,000 times.
Any ideas? Thanks!
Ian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
