Three examples of database optimization

In the maintenance of the old database often encountered very query, most of them are two reasons.
1) No index added
2) query statement causes index not to be used
3) Too many connections to the database

Example 1:

In a large calculation, it took half an hour each day to complete, the calculation of the process of careful analysis, found that the following statement took a long time

select sum(order_qty - delivery_qty - reduce_confirm_qty - lost_qty ) qty from circle_ordering where sku = ‘" . $sku . "‘ AND submit_status = 5 AND order_type = ‘AIR‘

By explain this statement, careful analysis of the database to know that there is no relevant index action on this query statement, which resulted in this SQL is a full table query. This creates a new index for the three columns (SKU, SUBMIT_STATUS, Order_type). After re-execution, the entire program was completed in only 10 minutes.

Example 2:

Select a.ebay_id, b.ebay_id as Ebay_subid, From_unixtime (a.ebay_paidtime) as Ebay_paidtime,                                      A.ebay_account, A.ebay_countryname, C.store_name as Warehouse, A.ebay_carrier, B.sku, B.ebay_amount, A.ebay_currency, B.ebay_itemprice, B.shipingfee, ((b.ebay_ite Mprice*b.ebay_amount) +b.shipingfee) as Total_amount, Ebay_postcode, B.item_promotion_ Discount_amount, B.ship_promotion_discount_amount from Ebay_order a left join Ebay_orde Rdetail b on (A.EBAY_ORDERSN=B.EBAY_ORDERSN) left join Ebay_store c o N (a.ebay_warehouse = c.id) where A.ebay_combine!=1 and (a.resend_org_ebay_id=0 or A.R  ESEND_ORG_EBAY_ID is null) and B.ebay_amount >0 and a.ebay_warehouse! = "and   A.ebay_user= ' Manwei '                                          and (A.ebay_paidtime between Unix_timestamp (' ". $astart." ') and Unix_time                                             STAMP (' ". $aend. ') or (A.ebay_paidtime not between Unix_timestamp (' ". $astart _p." ') and Unix_timestam  P (' ". $aend _p.") and a.shippedtime between Unix_timestamp (' ". $astart _p." ') and Unix_timestamp (' "                                        . $aend _p. ")";                                        if ($last _ebay_id!= ") $data. =" or a.ebay_id >= ' ". $last _ebay_id." ' "; $data. = ") Order by a.ebay_id, b.ebay_id";

Notice the condition of this complex query statement

First condition
(A.ebay_paidtime between Unix_timestamp (' ". $astart." ') and Unix_timestamp (' ". $aend.")
Because the Ebay_paidtime field has an index, if only this condition, the query is fast, the query is less than one second. However, because there are two conditions behind the use or, this results in a full table query for Ebay_order, and the table has 3百多万条 data, so the query is very slow.

According to the business requirements, we put three query conditions connected with or, respectively, to query, and finally linked together with a union statement. The efficiency of this query has been greatly improved. The modified query is as follows

$data 1 = "select". $fields _list.                                                       "From Ebay_order a LEFT join Ebay_orderdetail B on (A.EBAY_ORDERSN=B.EBAY_ORDERSN)                                    Left join Ebay_store c on (a.ebay_warehouse = c.id)                                           where A.ebay_combine!=1 and (a.resend_org_ebay_id=0 or a.resend_org_ebay_id is null) and                                           B.ebay_amount >0 and A.ebay_warehouse! = "and a.ebay_user= ' Manwei '  and a.ebay_paidtime between Unix_timestamp (' ". $astart." ') and Unix_timestamp (' ". $aend." ') "; $data 2 = "Select". $fields _list.                                                       "From Ebay_order a LEFT join Ebay_orderdetail B on (A.EBAY_ORDERSN=B.EBAY_ORDERSN)                                    Left join Ebay_store c on (a.ebay_warehouse = c.id) where A.ebay_combine!=1 and (a.resend_org_ebay_id=0 or A.resend_org_ebaY_ID is null) and B.ebay_amount >0 and a.ebay_warehouse! = "and a.ebay_user= ' Manwei ' and (A.shippedtime BETW Een unix_timestamp (' ". $astart _p." ') and Unix_timestamp (' ". $aend _p." ') and a                                            . Ebay_paidtime not between Unix_timestamp (' ". $astart." ') and Unix_timestamp (' ". $aend.")    )"; if ($last _ebay_id!= ") {$data 3 =" Select ". $fields _list.                                                       "From Ebay_order a LEFT join Ebay_orderdetail B on (A.EBAY_ORDERSN=B.EBAY_ORDERSN)                                    Left join Ebay_store c on (a.ebay_warehouse = c.id)                                           where A.ebay_combine!=1 and (a.resend_org_ebay_id=0 or a.resend_org_ebay_id is null) and B.ebay_amount >0 and A.ebay_wArehouse! = "and a.ebay_user= ' Manwei ' and a.ebay_id >= '". $last _ebay_id. "'   "; } $data = "(". $data 1.  ")"; if ($data 2 = "") $data = $data. "Union (". $data 2.  ")"; if ($data 3 = "") $data = $data. "Union (". $data 3. ")";

episode, when we analyze data2, regardless of the Shippedtime index, as long as the query shippedtime are all table query. Careful analysis of the original in the database design, the Shippedtime field is varchar, the program to save the timestamp of this type, naturally there is no way to use the appropriate index for our needs, the solution is to change the shippedtime by the ALTER statement to INT Type, and then add an index to this field. This problem of slow query is completely solved.

Example 3:

$data = $isfesdb->query($data);$quan = $isfesdb->num_rows($data);for($i=0;$i<$quan;$i++){{            ...            $vv             = "select goods_name, goods_weight from ebay_goods where goods_sn=‘".$sku[$i]."‘ limit 1";            $vv             = $isfesdb->execute($vv);            $vv             = $isfesdb->getResultArray($vv);            if(count($vv)==0){                                                  ...                            $sku[$i]   = str_replace(‘-FBA-FR‘,‘‘,$sku[$i]);                                                                ...                        }            ...}

From the code, this is just a very simple query, Ebay_goods also has an index, you should be able to query the results soon. But in fact the whole process runs down very slowly. Careful analysis of the reason is because the $quan number is too large, resulting in a for loop more than 10,000 times, which resulted in $vv this query 10,000 times. So it's not a performance issue to look up a single one, but if you repeatedly repeat such a query, it can cause performance problems.

The solution is to query the Ebay_goods full table before the For loop, log the table to an array, and then use the group's data in the For loop. This method is possible because the Ebay_goods array has only thousands of records.
The modification program becomes:

$vv = $isfesdb->query("select goods_sn, goods_name, goods_weight from ebay_goods");$vv_quan = $isfesdb->num_rows($vv);$vv_result = $isfesdb->getResultArray($vv);for($i=0; $i<$vv_quan; $i++) {        $goods_array[$vv_result[$i][‘goods_sn‘]] = array($vv_result[$i][‘goods_name‘], $vv_result[$i][‘goods_weight‘]);         }for($i=0;$i<$quan;$i++){...     if(!array_key_exists($sku[$i], $goods_array)){             ...             $sku[$i]   = str_replace(‘-FBA-FR‘,‘‘,$sku[$i]);                                                ...     } ... }

When we use the array method, the query is also more efficient than the old method several times. This is because the memory of our server configuration is large enough now, and PHP is running fast enough. The bottleneck is that PHP is waiting for MySQL query results. So we first use a query to make the database result into an array.

Three examples of database optimization