CODEWITHSUNDEEP
mysqlvarchar
chengzhiwei
chengzhiwei

Reputation: 101

Why mysql select varchar type is very slow

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

Upvotes: 5

Views: 3376

Answers (3)

Santosh Ghimire
Santosh Ghimire

Reputation: 3145

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.

Upvotes: 0

mvp
mvp

Reputation: 116367

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.

Upvotes: 1

Hanky Panky
Hanky Panky

Reputation: 46900

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

Upvotes: 2

Related Questions