时间戳

首页 > 解决方案 > 从 VBA 调用时，MySql 查询需要很长时间才能执行

mysql - 从 VBA 调用时，MySql 查询需要很长时间才能执行

问题描述

我正在尝试使用 Excel Vba 从 MySql 数据库中检索一些数据。一切正常……但 MySql 查询执行时间过长。

这是我的代码：

SELECT
  d.DATE,
  c.name,
  c.address,
  c.state_name,
  c.contact_no,
  d.AMOUNT,
  d.BY_NAME,
  d.NARATION,
  t.REMARK
FROM
  database1.data d
JOIN
  (
  SELECT DISTINCT
    cust_id,
    OR_NO
  FROM
    database1.ordbill
) o ON SUBSTRING_INDEX(database1.d.NARATION,
':',
-1) = o.OR_NO
JOIN
  database1.contact c ON o.cust_id = c.id
JOIN
  database1.total t ON t.VCH_NO = d.VCH_NO
WHERE
  d.PARTY_NAME = 'advance' AND(
    d.`BY_NAME` = 'Bank1' OR d.`BY_NAME` = 'CASH' OR d.`BY_NAME` = 'Bank2'
  ) AND d.DATE BETWEEN '2019-09-01' AND '2019-09-30'
ORDER BY
  d.DATE ASC `

标签： mysqlexcelvba

解决方案

假设您已经在表联系人 pk id 上建立了索引索引

    SELECT * 
    FROM `loans` 
    WHERE `date` >= '2019-11-25' 
      AND `date`<='2019-11-28' 
    AND `designation` LIKE '%sdf%'
why does this happen ?

SELECT d.DATE
  ,c.name
  ,c.address
  ,c.state_name
  ,c.contact_no
  , d.AMOUNT
  , d.BY_NAME
  , d.NARATION
  ,t.REMARK 
  FROM database1.data d JOIN (
      SELECT DISTINCT cust_id, OR_NO FROM database1.ordbill
  ) o ON SUBSTRING_INDEX(database1.d.NARATION,':',-1)=o.OR_NO 
  JOIN database1.contact c on o.cust_id=c.id 
  JOIN database1.total t on t.VCH_NO=d.VCH_NO 
  WHERE d.PARTY_NAME = 'advance' 
  AND (d.`BY_NAME` = 'Bank1' OR d.`BY_NAME` = 'CASH' OR d.`BY_NAME` = 'Bank2')  
  AND d.DATE BETWEEN '2019-09-01' AND '2019-09-30' 
  ORDER BY d.DATE ASC

确保你也有适当的复合索引 

table  data columns(PARTY_NAME, BY_NAME, DATE, VCH_NO )

还有一个索引 

table total  column (VCH_NO)

推荐阅读