Try looking at the documentation for "groupwise maximum".
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html -Sheeri On 5/5/06, Ian Klassen <[EMAIL PROTECTED]> wrote:
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
