A new table for each client in MySQL?

Question

Normally I would not consider this, but I wonder how this can be harmful?

My current setup:

• each client has up to 150,000 rows
• I am performing a full-text search on two columns.
• another column is the client identifier

As the table grows larger, searches become extremely slow, although, like most, each search needs to be performed on only 150,000 rows.

It is very difficult to ONLY look for client strings when using full-text indexes.

One option is to create a third full-text column that contains a composite customer identifier, plus characters that will not be found in full-text strings, and include this third column in a full full-text search. pah.

Is it VERY VERY wrong to set up a unique table for each client and just search on their table? In my tests, the search is very fast. How is this true after you start working with multiple MySQL servers?

Answer 1

Perhaps you should take a look at 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 is slightly different from the "table for each client", but it should meet your needs.

Answer 2

In general, partitioning should be preferable to manually splitting data into multiple tables. There are a few warnings with separation that you need to be aware of. In particular, foreign keys are not supported .

In your particular use case, there is one reason why you would like to use the “manual splitting” route: splitting statistics for full-text search :

Relevance is calculated based on the number of words per line, the number of unique words in this line, the total number of words in the collection, and the number of documents (lines) that contain a particular word .

If all data is stored in one table of tables (whether they are divided or not), data belonging to one given user influences the relevance ratings of all users.

Obviously, this does not apply if you never order the search results by relevance (i.e. if you are just looking for logical matches).