There are four tables:
A user base tables (each user data, mid don't repeat)
Mid (user ID)
The username (username)
The password (password)
ID gid (by region, corresponding to the third form of gid)
User type (type)
The status (user)
Createtime (create time)
Two, user details table (each user data, mid don't repeat)
Mid (user ID, corresponding to the first table of mid)
Realname (real name)
Gender (sex)
Nation (national)
Party (politics)
Education (education)
. (and so on 50 multiple)
Three, the district table (a data, each region gid not repeat)
Gid
Guildname district (name)
Type (type)
The statute (state)
Createtime (create time)
Four, data modification list
Id (id) since the growth
Mid (user id, a list for the first mid)
Modifytype (modify data types: mark of distinction number 1-7)
Modifydata (modify data)
Modifystate (revision status: 0 uncommitted, 1 has been submitted for review, 2 audit)
Modifytime (modified time)
Note: each user may have article N data, and the state, such as mid to 1 user, have modified the four types of data, then have a mid four data (modifytype respectively 1, 2, 3, 4), state modifystate may have 1 article is uncommitted 0, 2 May be stay 1, article 1 is the approved 2, (pictured)


Now demand is that the four joint query table and required part of the field of table 1, table 2 parts of the field, guildname table 3, table 4 conditions modifystate=1 all of the fields, and id as for moid, combined with the query, and to group by mid of table 4 (because a user may have multiple modifystate=1), the final data comes out, some users moid is empty, some users moid with data, it can be judged, which users have to modify the data, which users are not modify the data, and then you can according to the combination of the query the user mid + ever to modify the data sorting,

There seem to be two views, but as long as join and group by table 4, efficiency is very low, advice on how to promote efficiency, thank you

CodePudding user response:

With text posted ( don't map!
) the explain the select...
And
show index from

For analysis

CodePudding user response:

The building Lord give specific SQL execution plan, the amount of data and index, specific reference # 1 reply,

CodePudding user response:

For the MySQL database SQL Tuning tool is not much, but most of these tools is focused on performing program visualization or perform analysis, if you don't have a thorough knowledge of SQL Tuning, and not willing to spend extra effort to adjust the SQL, so these tools did little for you, if you crave provide one-click solution tool, the tool automatically adjust the SQL statement, without intervention Tosska SQL Tuning Expert for MySQL might be the only option, you

CodePudding user response:

Thank you, oneself solved part, remove the unnecessary fields, and adjusted the index, the PHP code was optimized at the same time, in the way of a display data, now many query speed increased