MySQL

Tables

[fashezee]

When creating a database in MySQL, is it recommended to have
large tables or many small tables? It there a critical point that
would determine which strategy to use?

I've been told that with large tables; index tables can be created
to speed up the search process; however, I am not sure it would be faster
then creating smaller tables?

Any gurus that can help?

[ergophobe]

Not a guru, but I know enough to shed some light here.

- large tables versus small. This is determined by the data you have. Lots of data = large tables.

- tables with lots of columns versus few columns. This again is determined by that data. In general you should "normalize" your database (see this article [databasejournal.com] or this one [databases.about.com] or this one [phpbuilder.com] if you don't know what normalization is). Sometimes people choose to denormalize a database *slightly* to speed things up or simplify program logic. In theory, tables should always be normalized to the point where they have the absolute minimum repetition and every column in a row is directly dependent on the primary key.

So you don't typically want

table: plays
1 ¦ Shakespeare ¦ Hamlet
2 ¦ Shakespeare ¦ MacBeth
3 ¦ Shakespeare ¦ Merchant of Venice

But rather

table: plays
1 ¦ 1 ¦ Hamlet
2 ¦ 1 ¦ MacBeth
3 ¦ 1 ¦ Merchant of Venice

table: authors
1 ¦ Shakespeare

- indexes. You should index on columns that you want to search on, but not on others. Basically, an index will dramatically speed up searches on large tables, but can dramatically slow down updates. If you are updating frequently, you don't want any more indexes than necessary.

Does that help some?

[fashezee]

Thanks for the tips and links.

TBMS - the issue were have on hand is:
Our search page has 5 options to choose. We want to create 5 different tables
so that if each option selected would query the respective table.

We thought of going this way since not all of the 5 options are
mandatory for the search. However, we are not sure whether this method
is the fastest. We are currently setting up some tests; but I thought I can get some insight here.

[slade7]

If you have one large table, how many records/columns would there be?

Typically in searching for records, I will have the search options determine what goes into the WHERE clause in the query, rather than have the options determine WHAT TABLE...

I commonly run and select from tables with 1000 to 30,000 rows over the internet, and on intranet apps I have one table that sports close to 800,000 rows.

[jamesa]

I find that queries are fastest when the fields used in the WHERE clause are all in one table.