Mark---[Optimization method for MySQL multi-table correlated queries]

For a Web site, the structure of the database is critical. That is, to facilitate storage (warehousing does not block), but also conducive to query (query does not lock the table). Website Database optimization Experience is a process of accumulation. The following is an example of how to optimize multi-table associated queries.

Now community sharing site is very hot, take the square-dimensional shopping sharing site examples. It is also a summary of the two Development square shopping sharing website, the master can fly over.

The key tables for shopping sharing are: Sharing table, picture table, document table, comment form, label table, classification table, etc.
The table around sharing is much, wow, that's a lot of AH. When we look at the details of an image, we need to display the information in the table above. Display the category that the picture belongs to, to the picture to hit the label, the picture comment, has the document also to display the file download information and so on. Let us 6 tables to the correlation query, of course, not so many associations to query the data, we can only query a table, how to say? The sharing table here is the main table, and we can create a cache field in the main list. For example, we call the Cache_data field, giving it the text type so that it can store a long string without exceeding the maximum storage of the field.

How does this cache field work? Create a share ID after adding a new message. If the user publishes a picture or a file, the picture information into the picture table, the file information into the file table, and then the newly generated picture or file information to write to the cache field. Similarly, if the user has the option to classify and tag, the corresponding information is also written to the cache field. For comments, there is no need to put all comments into the cache field, because you don't know how many records he has, you can save the latest 10 entries in the Cache field for display, so that the cache field becomes a two-dimensional or three-dimensional array, serialized and stored in the share table.

Array (' img ' = array (name = ' 123.jpg ', url = =  ' http://tech.42xiu.com/123.jpg ', width =  800,width  = =,), ' file ' = Array (name = ' Abc.zip ', Download_url =  ' http://tech.42xiu.com/abc.zip ', size +  1.2Mb,), ' Category ' = array (1 = = Array (id = = 5,name = PHP blog), 2 = = Array (id = = 6,name = php Technology blog), ' tag ' = = Array (Tag1tag2 ...), ' message ' = = Array (1 = = Array (ID, UID, name, content, time), 2 = = Array ( ID, UID, name, content, time), 3 = = Array (ID, UID, name, content, time), 4 = = Array (ID, UID, name, content, time),), or /For example, the above array structure, serialized into the database. UPDATE share SET cache_data=mysql_real_escape_string (serialize ($cache _data)) WHERE id=1;

So the query becomes simple, only need to query one line, take the cache field, deserialize it, extract the array information, and then display to the page. If it was the previous structure, it would have collapsed at hundreds of thousands of of the amount of data. The method of data caching may not be the best, if you have a better approach, you can learn from each other and discuss each other.

Mark---[Optimization method for MySQL multi-table correlated queries]