Search in many tables - speed

[IWeb]

Hello!

I am building a category based search engine with many entries.
Each category has it's own table.

Is this efficient?
Or is it faster when I put all categories in one table with a CatID?

The one-table solution would be easier for the search function...

thanks

[[deleted]]

"Is this efficient?"

No.

"Or is it faster when I put all categories in one table with a CatID?"

Not only faster, it is the only sane solution 🙂

[Bunkermaster]

hmmmm depends on database and on size of SQL query...
I'd say that if you keep the multiple tables solution, you should have a ref table with all your cat table names and loop on the ref table to generate the SQL query...

Important thing is to think about maintainability and DB server load. having a table for each cathegory may be a good solution today but will it be when you have a cat to add? and then how bad will your code be impacted?

my 2 cents

[[deleted]]

Splitting it up has no advantages, not even in speed, because the first limitation is always the categoryid, and that can be indexed so sequential scans are very rare.

[Bunkermaster]

the fun part comes when you gotta query on multiple distinct cathegory values to actually find your stuff 😛 but I aggree with vincente.

If I had to do this I'd go for one table