Trying to get rows from multiple tables

[edzillion]

I was given some code a while ago to get all the rows between certain timestamps and then order them by timestamp:

$QueryHIST_QUOTES = "
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM $currentTable t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
ORDER BY TIMESTAMP";

Originally I had the variable $currentTable = "PRICES_2008" but I now want to get rows from multiple tables, so I tried:

$currentTable = "PRICES_2003, PRICES_2004, PRICES_2005, PRICES_2006, PRICES_2007";

The thing is, that each table contains the same fields, and the exact same type of data, just for different years. I am beginning to think that this was not such a good idea, and that even if the tables end up being huge (the above five tables could possibly have 18.4 million rows and would grow over time) it is a lot more manageable to just have one.

What do you think? Can anyone help here?

Cheers
Ed

[ZydoSEO]

It depends on you application, amount of data, etc. but there be nothing to stop you from having a table for each year. You can do this using UNION. You can select from all 5 5ables individually and then UNION the 5 result sets together. For example, if you want to search across all 5 years worth of data to find rows in a time period you could do something like:

SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2003 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2004 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2005 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2006 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime
UNION
SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.*
FROM PRICES_2007 t
WHERE TIMESTAMP BETWEEN $starttime AND $endtime

As long as each individual select that gets UNIONed returns the same number of columns with the same names and same datatypes, you should be good.

Sorry. Couldn't test this. I don't have MySQL on my desktop. But off the top of my head, if you want to do selects over multiple tables with the same schema, UNION is probably the way to go.

PS: Put it in a stored procedure and call the stored proc from your code. It's typically a lot easier to modify procs. Each year you'll just have to go add the next year to the proc. Unless you're really good and make the proc look for table names of the form PRICE_YYYY and dynamically query them all writing results of each to a single temp table and then select the final result set from the temp table to return to the caller. Just and idea.

[edzillion]

hmmm
The logic makes sense but there are no rows returned when I run this:

"SELECT TIMEDIFF(TIMESTAMP, 1064444400) AS diff, t.* FROM PRICES_2007 t WHERE TIMESTAMP BETWEEN 1064444400 AND 1222332699 ORDER BY TIMESTAMP UNION SELECT TIMEDIFF(TIMESTAMP, 1064444400) AS diff, t.* FROM PRICES_2008 t WHERE TIMESTAMP BETWEEN 1064444400 AND 1222332699 ORDER BY TIMESTAMP";

Anyone know whats going wrong?

[edzillion]

Anyone?

To clarify: All the tables have the same number of columns with the same names and same datatypes.

[LifeinAsia]

Do you get any error message? What if you just SELECT on one table- is there actually data?