A new table per client in MySQL?

Question

Ordinarily I wouldn't consider this, but I'm wondering in what ways could this be detrimental?

My current set up:

• each client has up to approx 150 000 rows
• I'm performing fulltext searching over two columns
• the other column is the client's ID

As the table becomes bigger, searches are becoming woefully slow, even though, as most, each search only needs to be performed on 150 000 rows.

It's proving really hard to ONLY search a client's rows when using fulltext indices.

One option is to create a third fulltext column that's a composite of the client's ID, plus characters that won't be found in the fulltext rows, and include this third column in the total fulltext search. ugh.

Is it VERY VERY wrong to set up a unique table for each client, and just search on their table? The searches are very fast in my tests. How would this fair once you start needing more than one MySQL server?

Answer 1

Maybe you should give a look to this article:

http://www.mysql.com/products/enterprise/partitioning.html

and this one too:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

The logic it's a little diffrent from "a table for each client", but It should fit to your needs.