Mysql – Best way to query huge data

MySQL

First let me describe my case:

I need to store around 200extra detail of each user. I am using wordpress. Now there is a default function to save data associated with user. But i am not sure what should i use. In my case i will need some of data in 1 page. For example i need 20 data in one page and 100 data in another. User may need 20 data at once or 100 data at once.

I didn't designed my database table yet. After understand the best way of query i will design it. To get 200 data (fields) of a user if it is ok to make 200 search query then i will user wordpress default meta table. Structure is given below.

| umeta_id | user_id | meta_key | meta_value |

From structure above you can see wordpress save each data of a single user as a single row. SO in this case for one user's 200 data wordpress will save 200 row. And to retrieve the data i have to make 200 queries.

Now if it is better to get all data of user by a single query and save it in session then i will create my own table. Structure is given below.

| User_ID | User_detail_1 | User_detail_2 | User_detail_3 | …

In the above structure i can save all those 200 data in one row and can get all in one query and then save to session and user when needed somewhere.

Now in my site i dont need all data in one page. On one page i might need 100 data and in another page i might need 50 data. So considering my case should i save each data as a row(using wordpress meta table) and then make 100 queries for the page where i need 100 data and make 50 queries where i need 50 data?

Or i should use one query and get all the data, save in SESSION then use data from SESSION where 50 or 100 data is needed?

Best Answer

Thanks for the clarification. The first proposal is the infamous EAV, Entity Attribute Value. It is my daily pain as it is the model used by a 3rd party tool. If you want to have a fine list of why it is usually such a problematic model, please have a look at Bill Karvin's "SQL Antipatterns". You will have problems with data types, multiple values, enforcing integrity...

Therefore, I'm all in favor of the second model. If the volume of your data is really important, you can consider partionning, especially vertical partionning.

Related Question