5

400,000 records in the table

Table formation

id(int,pk)
title varchar(255)
body(text)

Query 1

select **title** from qa_question order by id desc limit 300000, 15;

Running time 15 Second

Query 2

select **body** from qa_question order by id desc limit 300000, 15;

Running time 1.8 Second

I want to know why

chengzhiwei
  • 101
  • 1
  • 4

3 Answers3

2

Simple, ORDER BY is executed first and then only LIMIT works. If you order by even id on so many records that means a large amount of data is being moved around (varchar and text). If you don't order there then your queries are fast because data is not being moved around like that. The more data there is, the more I/O requests to disk will be generated.

To answer the question in comments below.

Difference could be due to the fact that OP might be using MyISAM storage engine and TEXT is not stored in-row for that. VARCHAR is. See here. That filesystem storage could be the differnce in sorting time for storing in-row and not in-row. Also read this nice explanation

Also see this post

Community
  • 1
  • 1
Hanky Panky
  • 46,730
  • 8
  • 72
  • 95
  • Hanky, The question is not about the order by, but about how returning a different datatype makes the query slower. Do you know what causes the difference between the 1st and 2nd query? Both use `order by`, but one takes almost 1/10 of the other. Also the difference between the 3rd and 4rd query is quite significant (and again it's related to the data type being returned) – Augusto Jan 21 '13 at 08:27
  • Thanks Hanky, I thought that somehow the answer was related to the storage engine (although chengzhiwei didn't mention which engine he's using). – Augusto Jan 21 '13 at 09:49
  • @chengzhiwei Then the above mentioned fact explains it for me :) – Hanky Panky Jan 21 '13 at 10:51
1

Lets look closer at your query which has ORDER BY id DESC LIMIT 300000, 15.

What MySQL server has to do to execute it? First, it has to fetch every single record according to primary index on id in descending order. Note, that this has to happen 300k times. Once it reaches 300,000 count, only then server can start to output result rows, and it will be only 15 of them. If you had used not 300k initial offset, but something smaller, this query would have worked much faster.

Why query1 differs from query2 by factor of 10x? This is because columns of TEXT in MySQL are linked to parent table (not stored in it) and retrieving them requires additional lookup, but VARCHAR is stored in-table. In your case, TEXT column wins because you don't really need to pull your body column until you hit row 300,000 (server only pulls relatively small reference to body TEXT column in another invisible table). But in case of title column, server has no choice but to pull full title column, even if it is in range of 100 bytes, and this is why it is 10x slower.

It is hard to tell exactly. It could be because first query ran slow, but cached all table data in RAM, so second can run much faster. You should repeat all queries at least 3 times before you can make any conclusions.

My suggestion for you is to find out a way to replace LIMIT 300000, 15 with

WHERE id BETWEEN 300000 AND 300014

or equivalent if you can. In this case server will be able to make use of primary index and it will be lightning fast.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • @chengzhiwei yes, timings from a single run are misleading. Average of timings from running it for several times consecutively(in a loop) might give a better reading. – जलजनक Jan 21 '13 at 08:51
  • Which SQL statements are the same effect regardless of the first execution – chengzhiwei Jan 21 '13 at 08:52
  • I edited my answer to explain this. But, please consider using `BETWEEN` - it will bring your query to millisecond times no matter what. – mvp Jan 21 '13 at 09:22
0

It is because varchar allocates memory before the query is executed i.e. it uses static memory allocation. So, even if the title is shorter than 255 characters, it will reserve the memory for 255 characters.

While you can use VARCHAR CHARACTER SET utf8, alias of nvarchar, which is dynamic and uses the amount of memory according to the length of the title, in this case.

Since you have 400,000 records which is quite a lot, this causes greater running time while using varchar. So, my suggestion is to use VARCHAR CHARACTER SET utf8 and avoid using varchar.

Santosh Ghimire
  • 3,087
  • 8
  • 35
  • 63