CODEWITHSUNDEEP
mysqldatabaseperformance
Michele
Michele

Reputation: 1488

mysql performance one big tables vs many tables

I am working on a social network website, so i hope users will be a lot. I need to save tags (key | counter) for every user and i wonder if it's better to use 1) a big table vs 2) one really large table vs 3) splitted big tables.

1) this is an example for many tables implementation

table userid_tags (every user has it's own table)

key   | counter
-----   ---------
tag1  |   3
tag2  |   1
tag3  |   10

Query 1: SELECT * FROM userid_tags WHERE key='tag1'
Query 2: SELECT * FROM userid_tags

2) single table implementation:

table tags

key   | counter | user_id
-----   ------------------
tag1  |   3     | 20022
tag2  |   1     | 20022
tag2  |   10    | 31234

Query 1: SELECT * FROM userid_tags WHERE key='tag1' AND user_id='20022'
Query 2: SELECT * FROM userid_tags AND user_id='20022'

3) splitted tables implementation

table 1000_tags (user_id from 1 to 1000)

key   | counter | user_id
-----   ------------------
tag1  |   3     | 122
tag2  |   1     | 122
tag2  |   10    | 734

table 21000_tags (user_id from 20000 to 21000)

key   | counter | user_id
-----   ------------------
tag1  |   3     | 20022
tag2  |   1     | 20022
tag2  |   10    | 20234

Query 1: SELECT * FROM userid_tags WHERE key='tag1' AND user_id='20022'
Query 2: SELECT * FROM userid_tags AND user_id='20022'

Question for 3) what's a good split index? i used 1000 (users) following the instict

Upvotes: 0

Views: 321

Answers (2)

JodyT
JodyT

Reputation: 4412

Using option 2 is the correct way to handle this. You can still use partitions within the table though. All the information about using partition can be found in the MySQL documentation.

Splitting the table in partitions for every thousand users would look something like:

CREATE TABLE tags (`key VARCHAR(50), counter INT, user_id INT)
    PARTITION BY KEY(user_id) partitions 1000;

If the user_id would be 21001 you could start searching in the correct partition something like:

SELECT * FROM tags PARTITION (p22);'

Because the id 21001 would be in the 22nd partition. Check the link for more information.

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191799

2 is the right answer. Think about how you are going to maintain one table per user, or 1 table per 1000 tags. How Will you create/update/delete the tables? What if you have to make mass changes? How will you be able to figure out which table you need to select from? Even if you can, what if you need to select from more than one of those tables simultaneously (e.g. get the tags for two users).

Having the tables split up won't give you much of a performance benefit as it is. It's true that if the tables grow very large inserts may become slower because mysql has to create the keys, but as long as you have the appropriate keys look ups should be very fast.

Another similar solution would be to have a table for tags, a table for users, and a table that maps both of them. This will keep the tag cardinality small and if you're using an auto_increment surrogate key for both tables, the key length for both will be small which should make look ups as fast as possible with no restrictions on the relation (i.e. having to figure out other tables to join on for other users).

Upvotes: 3

Related Questions