473,387 Members | 1,404 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 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 1746
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: jacob nikom | last post by:
I would like to create data model for a group of stores. All stores in this group are very similar to each other, so it is natural to allocate one MySQL database per store. Each database is going...
0
by: R. Tarazi | last post by:
Hello, We are planning to reprogramm our search machine and website. Since we have call agents who query the same database as the customers do, we had the idea of splitting the database to 2...
9
by: elyob | last post by:
Hi, I'm looking at storing snippets of details in MySQL about what credit cards a business excepts. Rather than have a whole column for Visa, another for Amex etc ... I am looking at having a...
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
2
by: bwana.mpoa | last post by:
Hi, We're using a mySQL database as a replica of another (Sybase) DB for reporting purposes. The Sybase is part of a real-time mission critical system - hence the separate database where people...
7
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both...
110
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
3
by: 2401 members, members can post | last post by:
Dear Madams and Sirs, Ever had to split a website + SQL Tables ? Have a UNIX pc-linux-gnu on i686 + FEDORA 1) We have to split a sub-domain for the main domain name. 2) We have to build a...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

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.