[Resolved] SELECT from many tables - help please.

cheese

Hi All,

Just sitting watching the football, when I should be either working or asleep and I have just started to think ahead on a problem I am going to have.

I create a new table for every user as they create an account. They all end with 'usertable'. They are all the same structure.

Now at some stage I would like to do a SELECT from all of these tables to be able to look for records that may be of interest to other users.

Can we do;

SELECT species FROM *usertable WHERE species=elephant

Thanks in advance,

Neil.

tomhath

Why does every user need their own table?

cheese

Hi Tomhath,

There will be about 1000 users with up to 5000 records each.

I thought it would be faster for each user to search their own table. Most of the data is of no direct interest to any other user, but I would like to be able to harvest information for statistical reasons.

Cheers,

Neil.

tomhath

I doubt that separate tables would be faster than one table with an index on userid.

cheese

Ok,

As you probably know more about this stuff than I do, I will rewrite the pages concerned.

My reasoning was that if you have 1000 users with 5000 records, then every time you do a SELECT something FROM atable WHERE.... then 5,000,000 records would be scanned by each user, where as if you only have 5000 records then it would take less time than one table.

Am I reading this right.

Thanks,

Neil.

tomhath

Without indexes you would be right, all 5 million records would have to be scanned. But the index gives the database a way to find the 5000 records it needs without having to look at any others.

Some database servers even give you a way of keeping the related rows together (in Microsoft's SQL Server it's called a clustered index). There might be something like that in whatever dbms you're using. That slows down updates to the table a little bit, but it makes data retrieval very fast.

Roger_Ramjet

There is an overhead for every table in a db. This does not normally matter, but with 5000 tables expect your RDBMS to spit the dummy, whichever one you use.

drawbacks to many tables

how mysql uses indexes

cheese

Thanks for all that info. I will change to one table.

I suppose the next question would be how many rows of data can a table handle?

In theory, this would become an ever expanding database on the basis that, even if a user does cancel their account, I can't see data being removed from this table.

Thanks again,

Neil.

laserlight

I suppose the next question would be how many rows of data can a table handle?

You could take a look at How Big MySQL Tables Can Be, though that is primarily concerned with storage space than number of rows handled.

Roger_Ramjet

I suppose the next question would be how many rows of data can a table handle?

What is the absolute limit of the address space of your operating system? That would be the ultimate limit to the number of rows a db could ever handle.

cheese

Thanks everyone for all the help.

I am using Mandrake 9.2 at the moment and plan to upgrade to 10.x soon.

I have made the changes to indexed entries in one table.

Cheers,

Neil.