MySQL # of Tables

[a389951l]

I have a script that produces a table for every user - relatively small table for each user. My hosting company is telling me that is causing performance issues with the mysql server. I am not a mysql expert so I know that it is inefficient to have all of these tables but my host is shutting me down for this reason.

So, mysql experts I would like to know if my host is feeding me bull or not.

Thanks...

[dct]

Depends how many users you have, does seem like a very odd database design what are you storing in each table :confused:

[frankm]

So, mysql experts I would like to know if my host is feeding me bull or not.
Thanks...


No, your host is right.
every table , even an empty one, needs 3 files. (Data, Index and Structure).
I created a stupid table with just " id int; ":
-rw-rw---- 1 mysql mysql 0 May 14 00:27 bla.MYD
-rw-rw---- 1 mysql mysql 1024 May 14 00:27 bla.MYI
-rw-rw---- 1 mysql mysql 8550 May 14 00:27 bla.frm

that's giving me 3 files.
MYD => Data, empty
MYI => Index , already 1kb,
frm => table structure 8kb.

If you would create 10000 EMPTY tables, you will be using 10000 * (1+8) = 90.000 KB == 90 MB of data.
That's a lot of bytes for storing no useful data :-)

You'd better create one table with a (primary) key, something like
username char(16),
fill this field with the name you were giving to the table now, and your host will be pleased


g'luck

[a389951l]

Depends how many users you have, does seem like a very odd database design what are you storing in each table :confused:

I know lol. I bought the site and it had this script already. I am looking to change the code.