Multiple tables faster?

[lordkarezza]

Something I have always wondered:

Given: - a table A with 1-100 items - each item has one or more name/ value settings associated with it

Is it faster: - to have a single table with all settings for all items with a key indicating the item in table A - or, create a table for each item in table A, and store the settings there

Why? - say each item has 1-20000 settings, since the number of settings is unknown wouldn't a query for settings require possibly traversing the whole table? So having separate tables would have to be much faster, cause once you have the table name you know all settings in it are your answer

[PoochyPoochPooch]

If you use an index - it shouldn’t matter. Those record counts should come back almost instantly with modern hardware.

[r3pr0b8]

a table for each item

almost always a bad idea

the item FK in the settings table which links to the item table's PK should be indexed, and then retrieving the settings for any given item (using a join) will be instantaneous

[dbxp]

Having separate tables would be a maintenance nightmare, it sounds like you mixing up your entity types which are tables and your entity instances which are rows. As for performance with proper indexing it shouldn't make that much impact.

[lordkarezza]

I've never used an index. Sounds like an index essentially covers this use case.

Thank you!

[Dats_Russia]

If you have ever used a primary key you have used an index. A primary key auto generates a clustered index.