Query is taking too much time

I want to take your ideas regarding the below query:

我想对以下查询采取您的想法:

select a.expense_code, a.expense_date, a.expense_supplier_code, b.supplier_name, a.expense_discount, a.expense_payment_method, a.expense_payment_transfer_to, a.expense_advance, a.expense_status,
                            sum(c.expense_item_buy_price * c.expense_item_quantity) , d.account_name, a.expense_counter, a.expense_type, a.expense_saving_type, a.expense_payment_transfer_from
                            from expense_data a, supplier_data b, expense_item c, tree_data d
                            where a.expense_supplier_code = b.supplier_code and a.expense_payment_transfer_to= d.account_code
                            and a.expense_counter = c.expense_counter
                            and a.expense_date between '2013-01-01' and '2014-01-01' and a.expense_status = 0 or a.expense_status = 2 group by (a.expense_counter);

This query is taking so much time even though in the expense_data table, there are four indices:

即使在expense_data表中,也有四个索引,这个查询花费了很多时间:

1- Expense_code. 
2- expense_user_id
3- expense_supplier_code
4- expense_payment_transfer_from

I do not know why it takes so much time is it because of two much join or is it because too much indeces. Can you please suggest?

我不知道为什么需要这么多时间因为两次加入或者是因为太多的余地。你能建议吗?

4 个解决方案

#1

Could be that your where clause contains a logical error. Look at the last line (last OR condition):

可能是你的where子句包含逻辑错误。查看最后一行(最后OR条件):

where 
  ....
  and a.expense_counter = c.expense_counter 
  and a.expense_date BETWEEN '2013-01-01' AND '2014-01-01' 
  and a.expense_status = 0 
  or a.expense_status = 2 

That means "take records between dates etc. AND with status 0, OR take all records with status 2"

这意味着“记录日期之间的记录等,状态为0,或者记录状态为2的所有记录”

#2

To speed things up, you might want to try to make a combined index on a combination of the columns you join on. That index might be more useful than the four separate indexes you have now, although you can still keep those. Apart from those four fields, you may even want to experiment by adding status and/or expense_data to the index.

为了加快速度,您可能希望尝试在您加入的列的组合上创建组合索引。该索引可能比您现在拥有的四个独立索引更有用,尽管您仍然可以保留这些索引。除了这四个字段之外,您甚至可能希望通过向索引添加status和/或expense_data来进行实验。

#3

Recoding it to make the joins clearer (and removing that massive join where if expense status was all tables were joined) gives:-

重新编码以使连接更清晰(并删除大规模连接,如果费用状态是所有表已连接),给出: -

SELECT a.expense_code, 
    a.expense_date, 
    a.expense_supplier_code, 
    b.supplier_name, 
    a.expense_discount, 
    a.expense_payment_method, 
    a.expense_payment_transfer_to, 
    a.expense_advance, 
    a.expense_status,
    SUM(c.expense_item_buy_price * c.expense_item_quantity) , 
    d.account_name, 
    a.expense_counter, 
    a.expense_type, 
    a.expense_saving_type, 
    a.expense_payment_transfer_from
FROM expense_data a, 
INNER JOIN supplier_data b ON a.expense_supplier_code = b.supplier_code
INNER JOIN expense_item c ON a.expense_counter = c.expense_counter
INNER JOIN tree_data d ON a.expense_payment_transfer_to= d.account_code
WHERE a.expense_date BETWEEN '2013-01-01' AND '2014-01-01' 
AND a.expense_status = 0 OR a.expense_status = 2 
GROUP BY (a.expense_counter);

Looking at that it is important that you have an index on supplier_code on the supplier_data table, an index on expense_counter on the expense_item table and an index on account_code on the tree_data table.

看一下,在supplier_data表上有一个关于supplier_code的索引,在expense_item表上的expense_counter上有一个索引,在tree_data表上有一个关于account_code的索引。

I suspect you don't really want to return items with an expense status of 0 and an expense date in that range, with any record of expense status 2 irrespective of date so the following might be what you want:-

我怀疑你并不真的想要退回费用状态为0且费用日期在该范围内的物品,任何费用状态记录2都与日期无关,因此以下内容可能是您想要的: -

SELECT a.expense_code, 
    a.expense_date, 
    a.expense_supplier_code, 
    b.supplier_name, 
    a.expense_discount, 
    a.expense_payment_method, 
    a.expense_payment_transfer_to, 
    a.expense_advance, 
    a.expense_status,
    SUM(c.expense_item_buy_price * c.expense_item_quantity) , 
    d.account_name, 
    a.expense_counter, 
    a.expense_type, 
    a.expense_saving_type, 
    a.expense_payment_transfer_from
FROM expense_data a, 
INNER JOIN supplier_data b ON a.expense_supplier_code = b.supplier_code
INNER JOIN expense_item c ON a.expense_counter = c.expense_counter
INNER JOIN tree_data d ON a.expense_payment_transfer_to= d.account_code
WHERE a.expense_date BETWEEN '2013-01-01' AND '2014-01-01' 
AND a.expense_status IN (0, 2)
GROUP BY (a.expense_counter);

#4

You have to put OR condition in brackets:

你必须把OR条件放在括号中:

SELECT a.expense_code, 
   a.expense_date, 
   a.expense_supplier_code, 
   b.supplier_name, 
   a.expense_discount, 
   a.expense_payment_method, 
   a.expense_payment_transfer_to, 
   a.expense_advance, 
   a.expense_status, 
   SUM(c.expense_item_buy_price * c.expense_item_quantity), 
   d.account_name, 
   a.expense_counter, 
   a.expense_type, 
   a.expense_saving_type, 
   a.expense_payment_transfer_from 
FROM expense_data a, 
   supplier_data b, 
   expense_item c, 
   tree_data d 
WHERE a.expense_supplier_code = b.supplier_code 
   AND a.expense_payment_transfer_to = d.account_code 
   AND a.expense_counter = c.expense_counter 
   AND a.expense_date BETWEEN '2013-01-01' AND '2014-01-01' 
   AND (a.expense_status = 0 OR a.expense_status = 2)
GROUP BY a.expense_counter;