Why mysql select varchar type is very slow

Question

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

Answer 1

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

Answer 2

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.