Improve speed of query to mysql

Asked Nov 18 '17 at 05:56

Active Nov 18 '17 at 08:05

Viewed 36 times

I have a query that sums record from a column for a particular month based on username with latest record for particular user. But the query takes sometimes forever to execute. Can anybody suggest what I am doing wrong.

Select 
  SUM(t1.some_value) 
from
  tblDemo t1
where 
  t1.month='082017' and 
  t1.username='xyz' and 
  t1.table_no IN ('4a', '4b', '4c', '5a', '5b') and 
  t1.row_version = (
    Select max(t2.row_version) 
    from  tblDemo t2 
    where t2.inv_no = t1.inv_no
  )

The table has approx 5 000 records for each month. What can I do to improve speed?

edited Nov 18 '17 at 08:05

James Z

12,209
10
24
44

asked Nov 18 '17 at 05:56

Manish Tiwari

Possible duplicate of [Mysql improve SELECT speed](https://stackoverflow.com/questions/4775527/mysql-improve-select-speed) – Oguz Ozcan Nov 18 '17 at 05:57
1

Number one culprit is often ineffective or missing indexes. That's what `EXPLAIN` can help diagnose. – tadman Nov 18 '17 at 05:59
Thanks a lot tadman it resolved the issue and the difference is excellent. – Manish Tiwari Nov 18 '17 at 06:49
1

In my experience, an uncorrelated subquery will sometimes outperform a correlated one. Also, consider storing dates as dates. It can make a considerable difference when searching across a range. – Strawberry Nov 18 '17 at 08:09
Thank you @Strawberry will try that. – Manish Tiwari Nov 20 '17 at 06:33

Improve speed of query to mysql

0 Answers0