I have a MySQL 5.0 database which has about 10 million records (updated daily).

Is it more efficient to search:

(a) 10 tables each containing 1 million records, or

(b) 1000 tables each containing 10,000 records?

My own thoughts are that (b) is NOT as efficient as there are more opening and closing of tables, but I could be wrong.

Any advice would be much appreciated.

Well, that all depends on what you're searching for.
Generally speaking (a) is the most efficient imo

depends

generally speaking Id say that in design terms n tables containing similar data would be a contradiction of good practice.

if the table is properly designed, using appropriate indexes I don't think you would see any substantial performance discrepancy if the table contained 1 million, 10 million or god knows how many rows MySQL can support these days.

if your SQL query isn't particularly well crafted, and/or you are performing searches on non indexed columns then there could be significant problems, but if you are gettign probelsm with those then essetnailly you will have the same problems with your n table design..... you just wont notice is quite as obviously.

however it depends on your data.
its perfectly reasonable say to have n separate tables covering say each of n branches sales figures, its perfectly reasonable say if each table stored each years sales, ie the main criteria you are going to apply are always going to identify which table to search in. its not the way Id want to do it, but if it works for you there no reason why not.

However if your proposed design arbitarily splits the data into n tables, and the users may have a requirement to get data form any one of those n, or potentially more than one of those n tables then Id say stick with a single table design.

Thanks for your help.

Each record contains product information, i.e. for Electronics it would be: Manufacturer, Model, Description, Price, Shipping, Availability, SKU, etc.

I mentioned 10 tables because we have 10 seperate departments, i.e. Books, Computers, Electronics, Music, Movies, etc.

Depends on a lot of things, like your search criteria for example.
SELECT * on any of the above options will kill most database servers

I also doubt they are directly comparable, because there would essentially be different Entity relationships between those two databases. Either that or one of the options is hideously un-normalised, which would lead to difficulties in other areas as well as performance.

Part of the answer will also come down to keys. Updating a large table (1 million records) will require restructuring of the key set continuously (and possibly unnecessarily if unnormalised data is contained), whereas restructuring a smaller table key set will always take less time. (this answer is based on BTree algorithms).

Damn you people. Wishing my reply was "submitted" earlier...

I wish I'd gone into more detail now

If it's any help, it's for a price comparison search engine (we're doing a prototype website but can't decide on the DB structure), basically the same as pricegrabber, Kelkoo, shopping.com, etc.

So really you want to post a topic in the "Database Concepts & Design" thread for advice on design?

I would concur with that. It seems you need advice more on the E-R of your tables that on a particular MySQL problem.

I would say that
is probably better describe as "categories" and NOT "departments". All of the above are particular types of product. I imagine that I might model categories in their own table

I'm not convinced that I would....
a product is a product.. after all if soemone is enquiring about say "SQL" you may find relevant categories in books, software, clothing or whatever. I might sub categorise items out (ie have a child table which had book specific data in eg ISBN, author No Pages.... heck I might even use an EAV model for such detail) but I'd still keep the master product detail in one table
id include a category type int he product detail, referencing a categories table, which would probably have a tree structure in in (ie it should be possible to derive say publications | books | IT design, so I could search for anything with say MySQL in the title specifying the level of category (ie if I specified publications Id want to return all results for that category and ALL sub categories of each child, or I may want to specify MySQL in IT Design..... its down to you as to how you see your users wanting to retrieve information form you.

how much data you maintain yourself and how you see yourselves making your self different to the likes of Kelkoo or whoever. after all theres no point being a "mee too" if all you are doing is replicating what they currently do....

At the moment we've got the following:

Table "Departments" (contains 10 departments as described above)
Table "Categories" (Contains 100 categories, for example Electronics has 15 categories, i.e. Televisions)
Table "Subcats" (contains 800 sub-categories, for example LCD, i.e. Televisions > LCD)

Then we have the 10 individual department tables, i.e. Electronics, these contain the product lists from the various stores, also all tables are indexed. Now we're happy with the DB structure, it's just that we can't decide if it's best to have 1 table for all products, or 1 table per department, or 1 table per store (which could be upto 1000), hence the original question.

I hope I've explained it clearly enough.

i can tell you how to make up your mind

write the sql for searching for a product called "acme" (searching in product name, product description, product vendor, etc.) when
- there is one table
- there are 10 tables
- there are 1000 tables

i think you will find that writing the sql for 1000 tables is too difficult, so you can probably discard that option right up front

have you ever written sql that searches in 10 tables?

hint: you will need UNION

Yes, we've already done the prototype sql code to search the 10 tables and it works perfectly, we've used "INNER JOIN" is that what you mean by a UNION. We've loaded the 10 tables up with 1000 records each of dummy data and we can return any data we chose to search for in about 0.01 seconds.

However, we don't know what sort of time to expect for it to search 10 million odd records, or at least not until the website goes live. It could well be that what we've already done is sufficient enough but I thought I would ask the question just in case.

I recognise your username r937, I believe you have helped me before. Thanks.

INNER JOIN? not likely, not unless the same product exists in each of the 10 tables