dtj2ww9500 2015-12-22 22:11
浏览 26

在mysql db上优化sql选择

I've been handed the thankless task of cleaning up a custom PHP app built with the absence of any MVC or proper design patterns and am currently experiencing a massive performance hit on a view due to queries per row.

I'd like some general guideline on where to start optimizing and where my time would most benefit the loading issues.

Here is how it's working as handed to me. For example, each row is operating these functions:

$purchased = report::purchased_products_total($customer['No'],$from, $to);
$returned = report::returned_products_total($customer['No'],$from, $to);
$cust_inv = $purchased-$returned;
$on_hand_total = report::get_onhand_qty_total($customer['No'],$from, $to);
$log_sale_qty = report::get_logged_sale_qty($customer['No'],$from, $to);
$expected = $cust_inv-$log_sale_qty;
$salesman = report::get_salesman_by_code($customer['Salesperson_Code']);
$salesman_name = str_replace("'","",$salesman->Name);
$phone = str_replace("/","-",$customer['Phone_No']);
$name = str_replace("'","",$customer['Name']);

Example of his sql, here is the purchased_products_total function:

public static function purchased_products_total($no,$from=NULL,$to=NULL,$item=FALSE){
  global $db;   
  $data= $db->QuerySingleValue("SELECT sum(a.Quantity) FROM ".INVOICE_LINES." as a
  WHERE a.Type='Item'
  AND No NOT LIKE 'ZB%' AND No NOT LIKE 'ZE%' AND No NOT LIKE 'ZN%'
  AND No NOT LIKE 'ZP%'AND No NOT LIKE 'ZR%'AND No NOT LIKE 'ZZ%'
  AND a.Sell_To_customer_No='$no'
  AND a.Stock_No!='NULL'
  AND Document_Posting_Date>='$from' 
  AND Document_Posting_Date<='$to'

  "
 );

 return $data;  
 }

The Invoice Lines table is MyISAM.

I can post more of the functions if needed. I ran a performance test and mysql_query is being run about 3,500 times and is taking almost 3 min to operate.

  • 写回答

1条回答 默认 最新

  • dongyin8991 2015-12-23 00:28
    关注

    Be sure all your query fields are indexed. You can also use SUBSTRING(No, 2) IN('ZB', 'ZE', 'ZN', ...) to test for inclusion of the first two characters, rather than the long string of AND ... NOT LIKE ...

    If this pattern is very common, you could even break out the first two characters into a separate column, or make a "covering index" that is only two characters long.

    In general, do what you can to get rid of LIKE to speed things up!

    评论

报告相同问题?

悬赏问题

  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择
  • ¥15 这款软件是什么?需要能满足我的需求
  • ¥15 SpringSecurityOauth2登陆前后request不一致