PHP/MySQL - Splitting similar data to two tables and querying as a whole

[larry]

Ok I am working on building my skills to convert my apps to LAMP (read
I'm a semi noob), and there was one part I was thinking of. If I
create two identical MySQL tables (we'll say, invoice and invoicearc)
one will hold current period data and the other will hod out of period
data - previous years stuff, which is only used in queries <5% of the
time at most.

Now can I join these two tables to make one table when doing queries
that span the current and prior periods. I've read of joins where you
are linking references, like primary and external keys, but not one
where you are bringing tables of the same structure into a larger
query. I've googled around a bit but cant find the right syntax to
find a yea or nay on if it is possible.

I know I could have mmassive tables and put all of this stuff in there,
but I was thinking I coud improve speed if I were able to just bring in
the archive data only when needed.

[Mike Willbanks]

Larry,

I know I could have mmassive tables and put all of this stuff in there,
but I was thinking I coud improve speed if I were able to just bring in
the archive data only when needed.

SELECT * FROM tbl1, tbl2

obviously * is not great for performance so place the fields there.

Mike

[ZeldorBlat]

[e-mail] wrote:

Ok I am working on building my skills to convert my apps to LAMP (read I'm a semi noob), and there was one part I was thinking of. If I
create two identical MySQL tables (we'll say, invoice and invoicearc)
one will hold current period data and the other will hod out of period data - previous years stuff, which is only used in queries <5% of the
time at most.

Now can I join these two tables to make one table when doing queries
that span the current and prior periods. I've read of joins where you are linking references, like primary and external keys, but not one
where you are bringing tables of the same structure into a larger
query. I've googled around a bit but cant find the right syntax to
find a yea or nay on if it is possible.

I know I could have mmassive tables and put all of this stuff in there, but I was thinking I coud improve speed if I were able to just bring in the archive data only when needed.

select colA, colB, colC
from tbl1

union

select colA, colB, colC
from tbl2

Provided that the columns in both tables have the same data types.

[mdeering]

Create a VIEW and query it when you would like to query against both
your current and archived data...

http://dev.mysql.com/doc/mysql/en/create-view.html

[Andy Hassall]

On 17 May 2005 14:28:26 -0700, "mdeering" <go****@mdeering.com> wrote:

Create a VIEW and query it when you would like to query against both
your current and archived data...

http://dev.mysql.com/doc/mysql/en/create-view.html

"The CREATE VIEW statement was added in MySQL 5.0.1."

So not yet feasible if the data is worth anything - 5.0 is still beta.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[dracolytch]

"I know I could have mmassive tables and put all of this stuff in
there,
but I was thinking I coud improve speed if I were able to just bring in

the archive data only when needed."

To be honest, in terms of maintainability, I'd seriously reconsider the
practice of having an indexed archive flag instead of two tables.
Ultimately it does depend on your data size and what you're doing with
it. For normal add/update/delete/search stuff, if it's less than 10,000
records (and you're indexing properly), don't worry about it. I've had
real-world scenarios that had tables with 250,000 rows, and still
performed admarably on a desktop system.

~D