Hi all,

Anyone know any good articles on optimizing queries/table designs queriying over 17 million(or such size) records in the DB?

If anyone doesn't know of such articles anyone got any good tips or anything else?

Thanks
MK

    Hi,

    Yes there's database normalization, but there might also some other factors for such a large amount of records im sure.

    database normalization should always be used regardless of db size. But im talking specifically about a db with over 17million records, im sure there are some guidelines for dbs of such size.

    Indexing of course, using temporary tables in some intances etc...

    Thx
    MK

      Well some basics:

      Use a DBMS that's optimized for retrieval (I won't Mention anY nameS).

      Use padded char fields instead of varchar

      Index any column that's used (or could be used) in a where or order by clause.

      If a query is executed a decent percentage of the times the databased is accessed you could look into caching the results instead of requerying everytime. Generally speaking if you have x queries and the total number of queries executed in a day is n then it would be a good idea to look into caching any query that is executed more then x/n times per day.

        and another one instead of using a float field use 2 int fields store the number in one and the decimal in another, unless math is performed on the float during retrieval.

          Write a Reply...