Modoer list page performance analysis and optimization_PHP tutorial

In the page of http://www.modoer.org/beijing/item/list-8, the following 2 sql

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 amount of data is not large, you can still get by, but what if the amount of data is large? Taking my data as an example, they are subjectatt data volume and subject data volume



SQL operation results are as shown in the figure:

It only took 44.16 seconds to query the amount of data using SQL, and the website could not be opened at all. Isn’t the technology not tested or optimized? Compound statements are fun to use but the effect is very poor

After temporary optimization by me, I changed the following code in subject_class.php and the performance was greatly improved
if( $atts) {
     $attlist = array_values($atts);
     $num = count($attlist);
   if ($num>0){
                                   $or = ''; ROUP_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; ' attid='.$attid;
                                                                                                                                                                               
                                                                                                                                                         🎜>> >                                       where count='.$num;
                                                                           $query=$this->db->query($sql);$b=$ query->fetch_array();$sids=$b['sids'];
                                                                                                                     >

                        $this->db->where('s.sid', explode(',', $sids), '');
        }else{
                        $where = '';
                }

还有查询数量的地方
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;
                        }
        }


希望有用到的朋友可以改下，也只是暂时解决办法。另有问题请联系qq  272164179