one or many tables

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
What is a better method: creating one large table with a category field   or
several smaller table
distinguished by the category?
The tables have identical structures. The records are  retrieved  only form one
category at the


Re: one or many tables

geder s dorf wrote:
Quoted text here. Click to load it

One large table.

Re: one or many tables

Aggro wrote:
Quoted text here. Click to load it

Agreed; one large table with an index on `category` is better for almost
all purposes.

I would recommend creating an index for the category field.  That would
increase the chances that there is no significant performance cost for
using one table over multiple tables (depending on whether the queries
on category can make use of the index).

Geder does not specify what "better" means for his case -- that is, what
are his criteria.  Usually I assume it's for performance of queries.

I can think of one case where you'd find a performance gain by storing
data in separate tables per category:  dropping a table is usually
quicker than deleting a subset of rows from a large table.  So if
deleting all records of a given category is the most important operation
to optimize, then creating multiple tables may be better.

Bill K.

Site Timeline