DB/Table Optimization

[Manat]

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

[drawmack]

google for database normalization

[Manat]

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

[drawmack]

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.

[drawmack]

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.