CODEWITHSUNDEEP
mysql
prelite
prelite

Reputation: 1073

How can I get my MySQL query to run faster?

I got the following table structure for the table "plays":

enter image description here

In this table are store about 4 milion records and now a query similar to this one takes more then 6 seconds to retrieve data:

select * from plays where id_machine = 52 and cicle = 2 and play = 2450

Every cicle of plays is made of 7000 plays. So foo example cicle 3 will have 7000 play records with "play" column that will go from 1 to 7000.

I need to significantly reduce this time.

A friend of mine suggested me to convert play and cicle in indexes, but I'm not so sure.

Upvotes: 0

Views: 46

Answers (1)

Dipti
Dipti

Reputation: 565

Try to use composite index is also known as a multiple-column index like in your case (circle and play). It will give better performance.

The query optimizer uses the composite indexes for queries that test all columns in the index, or queries that test the first columns, the first two columns, and so on.

http://www.mysqltutorial.org/mysql-index/mysql-composite-index/

Upvotes: 1

Related Questions