List:General Discussion« Previous MessageNext Message »
From:emierzwa Date:June 30 2005 1:32pm
Subject:RE: Avoiding full table scans
View as plain text  
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
http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html
that said there was a 128 table limit, but I never tried that many.

Ed

-----Original Message-----
From: Alec.Cawley@stripped [mailto:Alec.Cawley@stripped] 
Sent: Thursday, June 30, 2005 2:56 AM
To: davykiash@stripped
Cc: mysql
Subject: Re: Avoiding full table scans

David Kagiri <davykiash@stripped> wrote on 30/06/2005 09:44:11:

> Hi
> 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
command 
to find out which SELECTs are doing full table scans, and add Indexes as

appropriate.

        Alec



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Avoiding full table scansDavid Kagiri30 Jun
  • Re: Avoiding full table scansAlec.Cawley30 Jun
  • Re: Avoiding full table scansGleb Paharenko30 Jun
RE: Avoiding full table scansemierzwa30 Jun