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...
Depends how many users you have, does seem like a very odd database design what are you storing in each table
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