How to optimise a SQL query with a Timestamp

Question

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)

nbk · Accepted Answer · 2021-08-29 20:26:39Z

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

Gordon Linoff · Accepted Answer · 2021-08-29 20:22:24Z

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.

poleszcz · Accepted Answer · 2021-08-29 20:09:22Z

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.

Collectives™ on Stack Overflow

How to optimise a SQL query with a Timestamp

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related