CODEWITHSUNDEEP
mysqlsqlperformanceloadingstatements
JB_
JB_

Reputation: 23

SQL Statement running Slow

I am having problems loading data from a log table. I'm trying to filter the table on a column that has a string. Below is the statement I'm using. I can pull data up to 4 weeks previously, but when i try to pull in more historic data it times out. I hav eextended the time out to 600 seconds, but stil the same mistake. So I presume there is a better way of wriing my statement?

In addition if possible I would like to have the most recent timestamp based on the value

Thanking you in advance. Joe

select 
dl.user_id, 
dl.timestamp,
dl.value,
dl.operation,
concat(concat(un.first_name, ' '), un.last_name) AS "User" ,
from data_log dl 
where  dl.timestamp >= date '2019-12-01' and dl.operation = 'update' and dl.values like 
'%"date_time"%'

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

For this query:

select dl.user_id, dl.timestamp, dl.value, dl.operation,
       concat(concat(un.first_name, ' '),
       un.last_name) AS "User"
from data_log dl 
where dl.timestamp >= date '2019-12-01' and
      dl.operation = 'update' and
      dl.values like '%"date_time"%';

You want an index on data_log(operation, timestamp, values). This might help the performance issue.

Upvotes: 1

Related Questions