down2323 2015-06-08 05:46
浏览 77

MYSQL重构

I am newer to setting up MYSQL queries. I have come up with a query that has a sub query, in a sub query to get the desired results. However, I am looking to speed the query up on my database as right now it is taking around 5 seconds to pull the information and do the work. Any help would be appreciated:

SELECT AVG( Total ) AS Average
FROM (

  SELECT Total, ID, MIN( DATE ) 
  FROM (

    SELECT SUM( Amount ) AS Total,  `TransactionDate` AS DATE,  `ClientId` AS ID
    FROM  `transactions` 
    WHERE  `ClientId` <>  ''
    GROUP BY  `InvoiceId` 
    ORDER BY DATE ASC
    ) AS Invoice

  GROUP BY ID
  ) AS AllInvoices

The query needs to find all transactions on a given invoice (unfortunately this is stored as one number in the database already), then group them and get the earliest invoice for each client, then average the totals. Looking to get the average first visit total in the end.

Unfortunately I can't provide all table information, it is imported from a third party and I am not at liberty to fully describe it out. For the little bit that I can share:

Field           Type          NULL  KEY   Default
Id              varchar(900)  NO    MUL   NULL
ClientId        varchar(1000) YES         NULL
TransactionDate datetime      YES         NULL
Amount          decimal(15,4) YES         NULL

The Transaction table currently has 241613 total rows. This could be more or less at any given point in time. These rows don't only contain what a customer paid, but also potentially salary payments and the like included.

However, The only index on table is with the individual transaction IDs themselves, not a value that I am currently pulling.

MYSQL Information:

innodb_version           5.5.43
protocol_version         10
slave_type_conversions
version                  5.5.43-0ubuntu0.14.04.1
version_comment          (Ubuntu)
version_compile_machine  x86_64
version_compile_os       debian-linux-gnu

展开全部

  • 写回答

0条回答 默认 最新

      编辑
      预览

      报告相同问题?

      悬赏问题

      • ¥15 Centos新建的临时ip无法上网,如何解决?
      • ¥15 海康威视如何实现客户端软件对设备语音请求的处理。
      • ¥15 支付宝h5参数如何实现跳转
      • ¥15 MATLAB代码补全插值
      • ¥15 Typegoose 中如何使用 arrayFilters 筛选并更新深度嵌套的子文档数组信息
      • ¥15 前后端分离的学习疑问?
      • ¥15 stata实证代码答疑
      • ¥50 husky+jaco2实现在gazebo与rviz中联合仿真
      • ¥15 dpabi预处理报错:Error using y_ExtractROISignal (line 251)
      • ¥15 在虚拟机中配置flume,无法将slave1节点的文件采集到master节点中
      手机看
      程序员都在用的中文IT技术交流社区

      程序员都在用的中文IT技术交流社区

      专业的中文 IT 技术社区,与千万技术人共成长

      专业的中文 IT 技术社区,与千万技术人共成长

      关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

      关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

      客服 返回
      顶部