CODEWITHSUNDEEP
mysqloptimizationwhere-clausebetween
Subhajit
Subhajit

Reputation: 894

MySQL taking too much time to execute

My query is simple but contains lots of data:

SELECT * 
FROM trackhistory 
WHERE key=14846 and 
    date between "2016-11-15 00:00:00" and "2016-12-16 13:47:02"

I have more than 10 million rows in this table.

My table structure is :

enter image description here

Please help me to optimize it. Its taking more than two minutes for execution.

I have used explain for the query , but didn't get any clue. enter image description here

Upvotes: 0

Views: 98

Answers (2)

Rick James
Rick James

Reputation: 142296

A composite INDEX(key, date) will be better than separate indexes.

See my index cookbook

Upvotes: 1

Nanne
Nanne

Reputation: 64409

First, add an index to Key, and see what happens. The syntax is somehting like this:

ALTER TABLE `trackhistory` ADD INDEX (`product_id`)

Then you could add a key to Date, but that will add some extra effort with inserting, and will take some diskspace. This will depend on your usecases, and is impossible to say if this is a smart thing to do.

Best thing is to try out your usecases.

(and for my sanity, if you have camel-case in your database, why not show those in your query is well. This just looks icky, having a Date field and having date in your query. I know it works, but.... )

Upvotes: 0

Related Questions