CODEWITHSUNDEEP
mysqlsqloptimization
MAERT
MAERT

Reputation: 79

How to optimise a SQL query with a Timestamp

I have a table with more than 500k rows and I tried to use an index to get the results faster. I have an index of _Date and _Time with BTREE and its only works when I query only the columns _Date and _Time. How can I get the results fast but include all columns? I think that the columns type TEXT are a problem because they do not have a length.

SELECT _Date, _Time FROM logsTable
 WHERE TIMESTAMP(_Date, _Time)
 BETWEEN "2021-08-29T13:04" AND "2021-08-29T15:04" 
 LIMIT 135, 15;

#THIS IS THE INDEX THAT ONLY WORKS ONLY WITH _DATE AND _TIME
ALTER TABLE logsTable
ADD INDEX `logsTable_DateTime_Index` USING BTREE (`_Date`, `_Time`) VISIBLE;

Table columns:

id int AI PK 
raw text 
type1 varchar(100) 
type2 varchar(100) 
desc text 
address1 varchar(100) 
address2 varchar(100) 
num varchar(100) 
_Date date 
_Time time 
serie varchar(100)

Upvotes: 0

Views: 1152

Answers (3)

nbk
nbk

Reputation: 49410

A solution is to use a generated column and index that

DESC is a reserved word, that should be always avoided as you did with _date or you need rto use backticks

finaly the LIMIT of rows withour an ORDER By is not a godd idea,, as you can't predict the outcome

create table logsTable
(
id int AUTO_INCREMENT PRIMARY KEY,
raw text ,
type1 varchar(100) ,
type2 varchar(100) ,
`desc` text, 
address1 varchar(100) ,
address2 varchar(100) ,
num varchar(100) ,
_Date date ,
_Time time ,
serie varchar(100),
mytime timestamp GENERATED ALWAYS AS (TIMESTAMP(_Date, _Time)) STORED 
, INDEX `logsTable_DateTime_Index` (mytime));    

EXPLAIN
SELECT _Date, _Time FROM logsTable
 WHERE mytime
 BETWEEN "2021-08-29T13:04" AND "2021-08-29T15:04" 
ORDER BY mytime
 LIMIT 135, 15;

id | select_type | table     | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                
-: | :---------- | :-------- | :--------- | :---- | :----------------------- | :----------------------- | :------ | :--- | ---: | -------: | :--------------------
 1 | SIMPLE      | logsTable | null       | range | logsTable_DateTime_Index | logsTable_DateTime_Index | 5       | null |    1 |   100.00 | Using index condition

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271013

First, I would suggest writing the query like this:

SELECT _Date, _Time
FROM logsTable
WHERE _Date = '2021-08-29' AND
      _Time BETWEEN '13:04:00' AND '15:04:00' 
LIMIT 135, 15;

And then you want an index on logsTable(_Date, _Time).

And finally . . . you are using LIMIT without an ORDER BY. That is not recommended. The results come back in indeterminate order and could vary from one run of the query to the next.

Upvotes: 1

poleszcz
poleszcz

Reputation: 165

Basically you need to assess which columns should be indexed knowing what search operations will be executed in this particular app.

Here is the answer how to index TEXT column: https://dba.stackexchange.com/questions/210403/how-do-you-index-a-text-column-in-mysql

The rest of columns is staightforward - but you need to evaluate if you need indexes on them as each index adds overhead to operations like inserting data.

Upvotes: 0

Related Questions