"MySQL" An interesting database query analysis.

This article is to make a car parts of the e-commerce website, the general scenario is a list.php page, the page lists some parts and statistics of the total number for paging.

Of course, the page can also specify the query criteria, such as the appropriate vehicle brand, vehicle, displacement, year, etc., a product may be suitable for a variety of vehicles.

So a table was built:

Goods_chexings table

The column name is: Vehicle Factory ID, car ID, displacement ID, model ID (the model is not too standard, the customer gave the data inside called models, so will be used, the actual year of the string, such as which year start to the end of which year), the ID of the accessory (the ID originates from the good table).

Vehicle Factory Card ID, car ID, displacement ID, model ID there is a hierarchical relationship, the level is: Vehicle Factory ID--Car ID---ID---Model ID, the table so built to be convenient to find it, sometimes only designated to the factory card or car system, let you list to fit accessories.

。。。 Redundancy, redundancy ha, easy to find.

On the PHP back-end code ha:

WHERE clause when looking for:

         Carbrand//  brand        //carxi//Car system               //carxing//displacement//naming not standard ... Check it out.        //caryear//model        //$carBrand, $carXi, $carXing, $carYear       $carBrand = intval ($_get[' Carbrand ');        if (!empty ($carBrand))        {                         $where. = ' and (e.changpai_id = '. $carBrand. ' or e.changpai_id =0) ';        }        $carXi = intval ($_get[' carxi ');        if (!empty ($carXi))        {                         $where. = ' and (e.chexi_id = '. $carXi. ' or e.chexi_id =0) ';        }               $carXing = intval ($_get[' carxing ');        if (!empty ($carXing))        {                        $where. = ' and (e.pailiang_id = '. $carXing. ' or e.pailiang_id =0) ';        }        $carYear = intval ($_get[' caryear ');        if (!empty ($carYear))        {                        $where. = ' and (e.chexing_id = '. $carYear. ' or e.chexing_id =0) ';        }

Then the body of the Select

$goods _sql = ' SELECT DISTINCT (a.goods_id), A.name,a.pics,d.cat_name '        . ' From  goods A left '                         . '  Left joins Goods_cat D on  a.goods_top_id=d.cat_id ';                         . ' Left join goods_chexings E on a.goods_id = e.good_id  '                          . $where. "ORDER BY". $sort _by. ' Limit ' $limit;

In general, goods table and goods_chexings table order of magnitude: goods thousands of, goods_chexings tens of thousands of.

But list.php page, if any conditions are not specified, waiting time about 10s, light ....

If you casually specify a car system, brand what, waiting time about fraction seconds, also light ....

To add, there are indexes on the goods_chexings table ....

See this can guess why the time gap is so big .... Join goods_chexings is useless when you do not specify a query condition, just query the goods table.

No query criteria are specified goods_chexings the index on the table does not work at all, full table scan ....

Modified query:

$goods _sql = ' SELECT DISTINCT (a.goods_id), A.name,a.pics,d.cat_name '     . ' From goods A left '                     . ' Left join Goods_ca T D on  a.goods_top_id=d.cat_id '; if (! ( Empty ($carBrand) && empty ($carXi) && empty ($carXing) && empty ($carYear))        {            $sql. = ' Left join Mega_good_chexi E on a.goods_id = e.good_id ';          }                                  $sql. = $where. ' ORDER BY ' $sort _by. ' Limit ' $limit;

Note: Write wildcard SQL statement writing habits, regardless of the time of the query does not work, may use the table first from or join,

If the condition that you want to query appears, add it inside the WHERE clause .... The resulting index does not work, and the full table is scanned.

MySQL parsing command used: EXPLAIN extended (SQL statement).

"MySQL" An interesting database query analysis.