0

I have a medium sized data set of about 4m rows.

Running my query:

SELECT id
     , var1
     , var2 
  FROM table1 
 WHERE date< "2019-10-29" 
   and date>="2018-10-29" 
   and company_id = 17 
 LIMIT 260;

returns 253 rows and takes duration = 0.171 / fetch = 15.22s.

But when I vary the query to limit 240 rows:

SELECT id
     , var1
     , var2 
  FROM table1 
 WHERE date< "2019-10-29" 
   and date>="2018-10-29" 
   and company_id = 17 
 LIMIT 240;

It only takes 0.25s/0.000s.

Why does the variation in limit drastically impact the fetch time?

  • An additional variation is if I restrict the date range to return less rows, this strangely ups the fetch time again (perhaps because the number or rows is less than the limit?)
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Laurence_jj
  • 646
  • 1
  • 10
  • 23
  • Which is mysql's version? What happens if you place **SQL_NO_CACHE** after each select? `select sql_no_cache id, var1, ...` – lainatnavi Oct 29 '19 at 16:27
  • sql_no_cache makes no difference, I think the mysql server version is 5.7.23 – Laurence_jj Oct 29 '19 at 16:31
  • Anyways you should look at *duration time*, not at *fetch time*. I don't see any big difference between the two times. – lainatnavi Oct 29 '19 at 16:37
  • practically I need the total time (duration+fetch) to be as small as possible – Laurence_jj Oct 29 '19 at 16:39
  • The *fetch time* does not depend on your query's performance. Please check this link for a clearer explanation: [link](https://stackoverflow.com/questions/9425134/mysql-duration-and-fetch-time) – lainatnavi Oct 29 '19 at 16:43
  • and yet the structure of the query **is** clearly impacting the total time of the query – Laurence_jj Oct 29 '19 at 16:44
  • 2
    Note that LIMIT without ORDER BY is fairly meaningless – Strawberry Oct 29 '19 at 17:00
  • adding `order by date desc` changes the time to 16.00s/0.00s, so it flips the duration and fetch. Does the order by statement occur after the where clause (i.e. orders the 253 rows)? – Laurence_jj Oct 29 '19 at 17:02
  • 1
    assuming you have index both columns correctly, I guess it might be related to index size. when you are reading 240 records you are reading less index pages. lets say the 241th record is created about a year ago and there are 1 million records created after that, your database server need to navigate more index pages to find those extra records which in turn gives you more fetch time. – AaA Oct 30 '19 at 07:12

1 Answers1

0

I seem to have fixed it by adding an index to the table:

create index var1_index on table1(var1);
Laurence_jj
  • 646
  • 1
  • 10
  • 23