Query to retrieve latest row from 8 tables at once?

[cranjled]

Hi,

I have 8 tables. I would like to retrieve the latest (newest) row from each table. I'd like to achieve this in a single, efficient query considering that each of the 8 tables will run into the millions of records each. In pseudo-code:

(SELECT * WHERE table = lastRowCreated)*8tables

And the returned value would equate to:

$latest = array(
                'record1' => array($latestRecordTable1), 
                'record2' => array($latestRecordTable2), 
                'record3' => array($latestRecordTable3), 
                'record4' => array($latestRecordTable4), 
                'record5' => array($latestRecordTable5), 
                'record6' => array($latestRecordTable6), 
                'record7' => array($latestRecordTable7), 
                'record8' => array($latestRecordTable8)
            );

Thanks a lot for any assistance!

Cranjed

[guido2004]

http://dev.mysql.com/doc/refman/5.1/en/union.html

[cranjled]

Thanks. This was the solution:

    $sql = "(SELECT * FROM level1)
            UNION
            (SELECT * FROM level2)
            UNION
            (SELECT * FROM level3)
            UNION
            (SELECT * FROM level4)
            UNION
            (SELECT * FROM level5)
            UNION
            (SELECT * FROM level6)
            UNION
            (SELECT * FROM level7)
            UNION
            (SELECT * FROM level8)
            ORDER BY id DESC LIMIT 8;";

[r937]

I'd like to achieve this in a single, efficient query

each SELECT in the UNION will return all (millions of) rows in that table

then all rows from all tables must be sorted together (requiring massive amounts of temporary workspace)

only then the top 8 can be retrieved

this is efficient ???????????????

and anyhow, what if one of those tables has a lot of recent rows while the others don't -- the latest 8 rows will all come from that table

thus the UNION doesn't even provide the right answer !!!


i'll make a small bet that 8 separate queries, assuming each table has an index on the date column, is what you really want

[guido2004]

How about this?

(SELECT 
     1 AS sort_col
   , t1.* 
 FROM t1
 ORDER BY datecolumn DESC
 LIMIT 1)
UNION
(SELECT 
     2 AS sort_col
   , t2.* 
 FROM t2
 ORDER BY datecolumn DESC
 LIMIT 1)
UNION
...
UNION
(SELECT 
     8 AS sort_col
   , t8.* 
 FROM t8
 ORDER BY datecolumn DESC
 LIMIT 1)
ORDER BY sort_col

[r937]

that's excellent, and will certainly outperform 8 separate queries

except of course if the subquery isn't allowed to use LIMIT

[guido2004]

that's excellent, and will certainly outperform 8 separate queries

except of course if the subquery isn't allowed to use LIMIT

Well, in that case they'll just have to upgrade to a decently recent version of MySQL

[cranjled]

Thanks a lot for the responses! Just wanted to add that I'd figured out how to further tweak the query. I'll have to look at that nice example above closely!! Anyway, here's the tweak, which does this: pulls a random record from each of the 8 tables (and seems to) ensure a single record is pulled from each. What say you guys about this one?

(SELECT * FROM level1 LIMIT 1) UNION (SELECT * FROM level2 LIMIT 1) UNION (SELECT * FROM level3 LIMIT 1) UNION (SELECT * FROM level4 LIMIT 1) UNION (SELECT * FROM level5 LIMIT 1) UNION (SELECT * FROM level6 LIMIT 1) UNION (SELECT * FROM level7 LIMIT 1) UNION (SELECT * FROM level8 LIMIT 1) ORDER BY RAND() LIMIT 8;

Appreciate your feedback(s)...thanks!!

[guido2004]

pulls a random record from each of the 8 tables

No it doesn't. It pulls the first record from each table.
If you want a random record from each table, put the ORDER BY RAND in each single SELECT.

(and seems to) ensure a single record is pulled from each.

Yes it does.

What say you guys about this one?

(SELECT * FROM level1 LIMIT 1) UNION (SELECT * FROM level2 LIMIT 1) UNION (SELECT * FROM level3 LIMIT 1) UNION (SELECT * FROM level4 LIMIT 1) UNION (SELECT * FROM level5 LIMIT 1) UNION (SELECT * FROM level6 LIMIT 1) UNION (SELECT * FROM level7 LIMIT 1) UNION (SELECT * FROM level8 LIMIT 1) ORDER BY RAND() LIMIT 8;

You can get rid of that LIMIT 8, because the result of all the unions is always 8 lines at the most.

This isn't at all what you were asking for in your OP though

[cranjled]

guido, thanks. You misunderstand my last posts. In fact, your example query has led to a visually noticeable performance increase in retrieving the needed records -- THANK YOU!

Yes, your first query does pull the latest record from each table; I agree. That clarified, I was referring to the next query I posted on this thread (the post at 22:53pm, which was a variation on your original query).

You're also right in that this was not the original question... but as it was something very similar that I ALSO needed to achieve, I thought I'd post the other query as well, so that both queries could be shown -- or shot down -- here. It's always good to find relatively like things together.

Anyway, here's the final 2 queries that I'm using, and if you can see a better way still, I'm happy to hear your advice! Did my best to format the queries, sorry they're not perfect.

// Get latest record from each of X tables in a single query.
(SELECT 
    1 AS difficulty, 
    level1.* 
 FROM level1 
 ORDER BY id DESC 
 LIMIT 1) 
UNION 
(SELECT 
    2 AS difficulty, 
    level2.* 
 FROM level2 
 ORDER BY id DESC 
 LIMIT 1) 
UNION 
...etc...
(SELECT 
    8 AS difficulty, 
    level8.* 
 FROM level8 
 ORDER BY id DESC 
 LIMIT 1)
ORDER BY difficulty;

// Get a single random record from each of X tables in a single query.
(SELECT 
    1 AS sort_col, 
    t1.* 
 FROM t1 
 ORDER BY RAND() 
 LIMIT 1) 
UNION 
(SELECT 
    2 AS sort_col, 
    t2.* 
 FROM t2 
 ORDER BY RAND() 
 LIMIT 1) 
UNION 
...etc...
(SELECT 
    8 AS sort_col, 
    t8.* 
 FROM t8 
 ORDER BY RAND() 
 LIMIT 1)  
ORDER BY sort_col;

[cranjled]

r937, I detect a bit of sarcasm in your post... usually, that's not all that nice, but I know you mean well. Just wanted to say thanks for jumping in and giving me a laugh with the "this is efficient ???????????????" comment... I appreciate the great deal of help you've given me here, thank you.