mysql - fetch time is taking time

Question

I am running below simple query,execution time is 1sec but fetch time is 30 sec. It contains totally 100 000 records

SELECT id, referrer, timestamp  
FROM masterstats_innodb 
WHERE video = 1869 AND timestamp between '2011-10-01' and '2021-01-21';

Index is created on video and timestamp column and even range partition has been created on timestamp table. Can anything be done to fetch result faster?

question from:https://stackoverflow.com/questions/65840643/fetch-time-is-taking-time

Answer 1

Please provide SHOW CREATE TABLE.

Plan A: INDEX(video, timestamp)

Plan B - slightly better because of being "covering":

INDEX(video, timestamp, referrer, id)

PARTITIONing will not help the performance of this query any more than indexing.

You say "it" contains 100K rows -- are you referring to the table? Or the just the number of rows returned. If 'table', then the index will help. If the 'resultset', then you are constrained by having to send so many rows. What will the client do with 100K rows?? Can the server condense the data (eg summarize it in some way)?