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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 2:56 AM To: [EMAIL PROTECTED] Cc: mysql Subject: Re: Avoiding full table scans David Kagiri <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
