Selecting from multiple MySQL tables

Question

I have a few tables that have similar fields but not exactly the same.

The same fields they have are description (text field) and modified (unixtime)

I would like to select the last modified items from these tables based on unixtime. I cant use UNION since the tables aren't the same and the multiple table select times out.

I've been trying to look for this but no luck, either people are using JOINS or SELECT A., B. FROM table A, table B

Answer 1

How different they are? Maybe you can get the common fields out:

select t1.name1 as name from table1
union
select t2.name2 as name from table2

Answer 2

Try this:

SELECT
    IF (A.modified > B.modified, A.modified, B.modified) AS modified,
    IF (A.modified > B.modified, A.description, B.description) AS description,
FROM
    (SELECT description, modified FROM A ORDER BY modified DESC LIMIT 1) AS A,
    (SELECT description, modified FROM B ORDER BY modified DESC LIMIT 1) AS B
LIMIT 1

However, it's pretty much the same as just doing two queries (only more complicated) so I wouldn't recommend it.

Answer 3

Try adding desc index on 'modified' if your select timesou, and use limit on select to return just one (last) row.

Then you can:

SELECT 
    A,B,C,D, desc, modified 
FROM 
   TABLEA
UNION ALL 
SELECT 
    CAST(E as <A type>), CAST(F AS <B type>) ..., desc, modified   
FROM 
   TABLE B