You could leave your separate tables by region and create a MERGE
table over them. Your application only needs to reference the MERGE
table name, eliminating the need for your recompiles and MySql will
Handle the underlying tables. You can add or remove tables from the
MERGE at any time. I did notice a user comment in the online doc
that said there was a 128 table limit, but I never tried that many.
From: Alec.Cawley@stripped [mailto:Alec.Cawley@stripped]
Sent: Thursday, June 30, 2005 2:56 AM
Subject: Re: Avoiding full table scans
David Kagiri <davykiash@stripped> wrote on 30/06/2005 09:44:11:
> I our database there is one table that will grow into tetrabytes
> within a short time.
> I would like to know how i can reduce full table scans.
> I have used separate tables as per region for now but the problem is
> if i create a new table i am forced to recompile the application.
> I can easily avoid this by using one table and an extra field to
> flag which region a paricular transaction happened.However i need
> ideas of how i can avoid full table scans because slow queries are
> unacceptable. it will also give me more flexibility in writing reports
> I will gladly appreciate any links that are specific to this problem
> and case studies.There people who need to be convinced
The answer to your question is Indexes. Ensure that you have indexes on
your tables for all the different searches you do. Use the EXPLAIN
to find out which SELECTs are doing full table scans, and add Indexes as
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql