Modoer list page Performance Analysis and Optimization

On the page of the http://www.modoer.org/beijing/item/list-8, the following 2 SQL statements are executed

SELECT s. sid, pid, catid, domain, name, avgsort, sort1, sort2, sort3, sort4, sort5, sort6, sort7, sort8, best, finer, pageviews, reviews, pictures, favorites, thumb, aid, map_lat, map_lng, c_tel, c_dz FROM modoer_subject s left join modoer_subject_shishang sf ON (s. sid = sf. sid) WHERE exists (SELECT 1 FROM modoer_subjectatt st WHERE s. sid = st. sid AND attid = 173) AND exists (SELECT 1 FROM modoer_subjectatt st WHERE s. sid = st. sid AND attid = 4) order by finer desc limit 0, 20



Select count (*) FROM modoer_subject s WHERE exists (SELECT 1 FROM modoer_subjectatt st WHERE s. sid = st. sid AND attid = 173) AND exists (SELECT 1 FROM modoer_subjectatt st WHERE s. sid = st. sid AND attid = 4)

If the data volume is small, it will continue to work together. What if the data volume is large? Take my data as an example. The data volume is subjectatt and subject respectively.
 
 

SQL running result
 
It took only 44.16 seconds for a single SQL statement to query the data volume, and the website could not be opened. Isn't the technology not tested and not optimized? Composite statements are easy to use, but have poor results.

After my temporary optimization, subject_class.php can get rid of the following code, greatly improving the performance.
If ($ atts ){
$ Attlist = array_values ($ atts );
$ Num = count ($ attlist );
If ($ num> 0 ){
$ Or = '';
$ SQL = 'select GROUP_CONCAT (sid) sids from (
Select count (sid) count, sid from (
Select sid, attid from modoer_subjectatt where ';
Foreach ($ attlist as $ attid ){
// $ This-> db-> where_exist ("SELECT 1 FROM dbpre_subjectatt st WHERE s. sid = st. sid AND attid = $ attid ");
If ($ attid <1) continue;
$ SQL. = $ or. 'attid = '. $ attid;
$ Or = 'or ';
}
$ SQL. = ') as temp
Group by sid) as temp1
Where count = '. $ num;
}
$ Query = $ this-> db-> query ($ SQL); $ B = $ query-> fetch_array (); $ sids = $ B ['sids'];
// $ Where ='s. sid in ('. $ sids .')';

$ This-> db-> where ('s. sid ', explode (', ', $ sids ),'');
} Else {
$ Where = '';
}

And the number of queries
If ($ atts ){
$ Attlist = array_values ($ atts );
$ Num = count ($ attlist );
If ($ num> 0 ){
$ Or = '';
$ SQL = 'select count (*) as nums from (
Select count (sid) count from (
Select sid, attid from modoer_subjectatt where ';
Foreach ($ attlist as $ attid ){
// $ This-> db-> where_exist ("SELECT 1 FROM dbpre_subjectatt st WHERE s. sid = st. sid AND attid = $ attid ");
If ($ attid <1) continue;
$ SQL. = $ or. 'attid = '. $ attid;
$ Or = 'or ';
}
$ SQL. = ') as temp
Group by sid) as temp1
Where count = '. $ num;
}
}


Hope that useful friends can change it, but it is only a temporary solution. If you have other questions, contact qq 272164179.