Shulou(Shulou.com)06/01 Report--

When maintaining the old database, we often encounter extraordinary queries, most of which are for two reasons.

1) No indexing

2) the query statement causes the index to be unused

3) too many connections to databases

Example 1:

In a large calculation, it used to take half an hour to complete every day. After a careful analysis of the calculation process, it was 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'

Through the explain statement, careful analysis of the database to know that there is no relevant index on this query statement, which leads to the sql is a full-table query. So create a new index on these three columns (sku, submit_status, order_type). After re-execution, the whole program was completed in only 10 minutes.

Examples 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_itemprice*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_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.ebayside username (a.ebay_paidtime between UNIX_TIMESTAMP ('". $astart."') And UNIX_TIMESTAMP (''. $aend.') Or (a.ebay_paidtime not between UNIX_TIMESTAMP ('". $astart_p."') And UNIX_TIMESTAMP (''. $aend_p.') And a.shippedtime between UNIX_TIMESTAMP (''. $astart_p.') And UNIX_TIMESTAMP ('". $aend_p."') "; if ($lastworthy ebaysides) $data. =" or a.ebay_id > ='. $last_ebay_id. ""; $data. = ") order by a.ebay_id, b.ebay_id"

Note the conditions of this complex query statement

The first condition

(a.ebay_paidtime between UNIX_TIMESTAMP ('". $astart."') And UNIX_TIMESTAMP ('". $aend."')

Because there is an index in the ebay_paidtime field, if there is only this condition, the query is very fast, less than a second at a time. But because there are two more conditions that use or, this leads to a full table query of ebay_order, which has more than 3 million pieces of data, so the query is very slow.

(there is a saying that verifying that using or between two identical fields does not result in a full table scan, only when different fields are self-built using or. But I haven't verified it.)

According to the business requirements, we take apart the three query conditions connected with or, query them separately, and finally connect them with Union statements. In this way, the efficiency of query has been greatly improved. The modified query is as follows

$data1 = "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.ebayside username installed manwei' and a.ebay_paidtime between UNIX_TIMESTAMP ('". $astart."') And UNIX_TIMESTAMP ('". $aend.") "; $data2 =" 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.ebayside username installed manwei' and (a.shippedtime between UNIX_TIMESTAMP ('". $astart_p."') And UNIX_TIMESTAMP (''. $aend_p.') And a.ebay_paidtime not between UNIX_TIMESTAMP (''. $astart.') And UNIX_TIMESTAMP ('". $aend."') "; if ($lastworthy ebayment) {$data3 =" 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.ebayside username installed manwei' and a.ebay_id > =''. $last_ebay_id. "'" $data = "(". $data1. If ($data2! = ") $data = $data. "union." $data2. If ($data3! = ") $data = $data. "union." $data3. ")"

Interlude, when we analyze data2, no matter how to index shippedtime, as long as the query shippedtime is a full table query. Careful analysis to know that the original database design, the shippedtime field is varchar, the program to save the timestamp of this type, there is no way to use suitable for our needs of the index, the solution is through the alter statement to change the shippedtime to the int type, and then add an index to this field. In this way, the problem of slow query is completely solved.

Example 3:$data = $isfesdb- > query ($data); $quan = $isfesdb- > num_rows ($data); for ($iExecutabilityexecute ($vv); $vv = $isfesdb- > getResultArray ($vv) If (count ($vv) = = 0) {... $sku [$I] = str_replace ('- FBA-FR','',$sku [$I]) ...}...}

From a code point of view, this is a very simple query, ebay_goods also has an index, should be able to query the results soon. But the whole process is actually very slow. The reason for careful analysis is that the number of $quan is too large, resulting in more than 10000 for loops, which results in the query $vv being executed 10000 times. So there is no performance problem if you check a single query, but if you repeat such a query many times, it will cause performance problems.

The solution is to query the full ebay_goods table in front of the for loop, record the table to an array, and then use the prime array data in the for loop. Because the ebay_goods array has only a few thousand records, this method is feasible.

Modify the program to:

$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 ($iTun0; $I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.