471,336 Members | 976 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,336 software developers and data experts.

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

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.

Jul 17 '05 #1
5 1644
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
Jul 17 '05 #2

l...@portcommodore.com 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.

Jul 17 '05 #3
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

Jul 17 '05 #4
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
Jul 17 '05 #5
"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

Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by jacob nikom | last post: by
reply views Thread by R. Tarazi | last post: by
9 posts views Thread by elyob | last post: by
2 posts views Thread by bwana.mpoa | last post: by
3 posts views Thread by 2401 members, members can post | last post: by
Atli
6 posts views Thread by Atli | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.