How to make faster queries

Asked

Viewed 444 times

-2

How can I make faster queries to a table MySQL with 3 million records, the table has only 4 columns, and it takes me almost 5 seconds to return data. The fields I search have index. To Query returns close to 50,000 records and is executed by MySQL WorkBench. I leave some more information below and if necessary I can provide more information.

Query:

SELECT campo2, campo3, campo4 FROM tabela2 
WHERE campo2=25 AND campo3>='2016-09-01 00:00' AND campo3<='2016-10-01 00:00'

Structure Table 2:

id     - bigint
campo2 - int         - index
campo3 - timestamp   - index
campo4 - float

Can someone help me with something?

mysql sql database query time

  • It is difficult to answer without you posting more information like the template of the tables involved and query that is slow.

  • Friend, I believe that you can improve considerably the execution time of the query by changing the way of comparing the dates by the BETWEEN command. It would be interesting to post the model of the bank so that we can analyze the problem better.

  • @Gokussjgod and tinkering with my.ini might solve something?

  • Right. Do you need to bring all the fields in the table even? (SELECT * FROM table2...)

  • @Geovanedasilvadejesus no, I can leave the id

  • Please avoid long discussions in the comments; your talk was moved to the chat

Show 1 more comment

1 answer

1

I don’t see how to optimize much more your query in the question of database, it is a simple query with appropriate index. But anyway take a look at the execution plan to confirm.

Assuming the query was executed directly in the BD, the only other bottleneck I can imagine (other than some obscure configuration that has been made) is the hardware.

Browser other questions tagged mysql sql database query time

You are not signed in. Login or sign up in order to post.