DataBase Efficiency

General rules of thumb?

[Nick_W]

Hi all,

Normalization is a must when designing a Database, but what other rules/guidlines are there to consider?

I'm particulary wondering if, after normalization there is anything to be gained by grouping numerical data and textual data in seperate tables?

Tips from the DB design guys most welcome ;)

Nick

[ggrot]

If your database is mostly reads and not writes, it can sometimes make sense to forget full normalization and keep a cached copy of intermediate results: results that are directly dependant on normalized data, thus repetitive data. As long as you make sure your code maintains these results, not having to do complex calculations repeatedly can be much more efficient.

Also, make sure you build good indices based on what you need. While adding to a database with no indices takes constant time, it also requires order N time (time is linear with respect to number of entries - if you double entries, it takes twice s long) to find a record, whereas with a index, adding and searching take order lg N (if you double the number of entries, it takes a constant number of time units longer).

[lorax]

DON'T oversize your db fields and DO choose the most appropriate field type. You don't need a TEXT field for a person's name when a VARCHAR(48) will cover most names.

I can't think of any advantage to seperate textual and numerical data.

[sun818]

Any opinions on separating fixed length fields and variable length fields or binary fields into different tables?

I think indexes are important and need to be coordinated with the SQL being executed. You don't just index the field with the greatest number of distinct values if that's the field the SQL is utilizing.

[lorax]

Another important item (at least with MySQL) is to optimize the database often. It clears out the junk and helps keep the db lean & mean.

[Nick_W]

>clears out the junk

What kind of junk might it accumulate?

Nick

[sun818]

Here's a mySQL link on optimizing the database:

[mysql.com...]

[lorax]

>> What kind of junk.

"To coalesce fragmented records and eliminate wasted space resulting from deleting or updating records..."

[mysql.com...]

[Fischerlaender]

It can have an enormous effect to use indexes the right way.

In most cases you'll need an index for any column that is searched for in a WHERE clause. But if you do a lot of inserts, then every index is slowing your database app down.

When your table is growing your index will also. If it gets to big it won't stay in RAM any longer and this slows your queries down again. Often it is possible to restrict the index of VARCHAR fields to about ten characters (as an example) instead of indexing the whole field. This decreases your index.

[sun818]

every index is slowing your database app down.

That's true. For big data loads, drop all indexes associated with the table. Your data load will go faster because there is no index to update. Once you're finished with your data load, re-create the index.

[lorax]

...drop all indexes associated with the table. Your data load will go faster because there is no index to update. Once you're finished with your data load, re-create the index.

I suppose if I were working with databases that would benefit from this technique I would also take the time to build the tools necessary to automate as much of the mundane tasks as possible.