[RESOLVED] MySQL Table Questions

[rulian]

Hello All,

I'm developing a data bank of information for a user log app for a marketing firm.
Users can easily log a few dozen logs in a matter of a few minutes, which can make my log table huge if a few thousand (which is quite possible) are logged on, even if not simultaniously.

I'm considering creating mysql tables for each specific user breaking out all user specific information into the user's specific tables.

Is there a limit on how many tables a mysql database can hold? is this even a good practise or should I look into other alternatives. I'm not quite sure if having a database with tables for just user interaction seperate form a database with all site tables would kill my server given all the connection have just X2.

I've created a sample database in my mysql staging server and populated it with just over a million rows, the queries are coming in a a second or two, or three which I dont consider bad considering all the rows, however, if there will be constant user interactiion, seek times of even 1/2 second will kill the site.

Any other advice on techniques for distribution information on a "grand scale" mysql driven site would also be appreciated, pitfalls and such. This is the first time I'm developing something for several thousand users so I'm not quite sure how to handle the same things I did on smaller site.

Thanks

[Piranha]

You are going about this in the wrong way. You should NOT have different tables for different users. MySQL and other RDBMSes are built to handle millions of rows, so it is not a problem.

Instead you should optimize the database and queries. Do you have the right columns indexed? Have you optimized the queries? Are there queries you use that takes longer time to run? Do you do unnessecary database queries like below? These questions are the first ones to consider when optimizing the database.

$sql = "SELECT something FROM somewhere WHERE a = b"
$result = mysql_query($sql);
foreach row in the database
{
    $newsql = "SELECT something else FROM somewhere else"
    $newresult = mysql_query($newsql)
}

The example above might look like 2 database queries. But if you get 100 results from the first one it is 101 queries that have to be run. Including everything in the first query would only require 1 query.

When you have managed the above you should consider if the database server is fast enough and set up properly. But don't do the mistake of trying to use different tables for different customers/items/anything.

[rulian]

Thanks Piranha,

It has become evident to me my original idea isnt' practical by any means. Like someone else said, distributing the information doesn't reduce the amount of information, it simply scatters it.

I'm gathering information on optimizing databases and queries, so any additional advice or warnigns would be helpful.

One question left, at what point should I expect even at optimal performace for mysql to start bugging down. It's not of my immediate concern, but down the line I might have to answer that question

[Piranha]

How long is a rope?

It is simply impossible to say, it all comes down to the computer, network, optimization and what kind of queries you run. With a bad computer, no optimization and bad queries it could be trouble with a couple of thousand rows in a couple of different tables. With a good dedicated server, good optimization and easy queries you can handle millions (if not billions) of rows without big trouble.

[rulian]

I like that answer, that settles that