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


l...@xxxxxxxxxxxxxxxxx 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.

.

Relevant Pages

  • Re: Optimizer Issues with 10.00.xC8
    ... First, if the selectivity of colA is so good, but with millions of rows the default resolution of the data distributions may be too low. ... Whereas for ColC, nearly 60% of the rows have a value of ColC = 0. ... what I've found is that queries run from dbaccess always choose the best index. ... The best solution for this is to UPDATE STATISTICS LOW...DROP DISTRIBUTIONS immediately before the truncate/load operation, run that initial round of time sensitive queries without data distributions, and reinstate the distributions as soon after the initial round of queries as is practical. ...
    (comp.databases.informix)
  • Re: id record for each table
    ... row ordering the rows by colA, colB descending, colC, where (colA, colB, ... No identity field but just a internal flag or hide field which computes each record. ...
    (microsoft.public.sqlserver.programming)
  • Re: Query Error
    ... INSERT INTO (colA, colB, colC) ... but I am getting an error message: runtime error 3134: SYNTAX ERROR IN INSERT INTO statement. ...
    (microsoft.public.access.queries)
  • Re: converting from long to double
    ... decimals). ... Append query - like this (say colC is the converting column): ... SELECT colA, colB, Val ...
    (comp.databases.ms-access)
  • Re: Join on alias field values on-the-fly
    ... Ohh becuase we can have a scenario where there is other things in ColC ... where ColA and ColB match. ... So use that in your join: draw a line from ColA to ColA, and from ColB ...
    (comp.databases.ms-access)