Mysql – Select query is too slow

innodbMySQL

I am facing a really strange problem with my database. I have a table with less than 20 columns. When i run a select * query, it's taking almost 6 to 7 seconds to execute. I couldn't identify the issue and tried selecting each columns one by one. By the way, total number of rows are just 269.

Finally i found 2 columns which causes the issue. Both these columns store some kind of description and the datatype is MEDIUMTEXT.

SELECT LocalDescription FROM test.hotels where Id > 0;
SELECT LocalFacilitiesDescription FROM test.hotels where Id > 0;

It took between 5 to 6 seconds to finish the execution. There is no delay for the rest of the columns.

Best Answer

It has to do with the way VARCHAT/TEXT/BLOB field are handled.

I personally observed this in an RDS instance

I had taken a table with 25 million rows which had a TEXT field in it and ran this

SELECT DISTINCT LENGTH(colname) FROM tblname;

and I saw just 2 numbers, 3 and 10.

I converted the field from TEXT to VARCHAR(10). What this did was increase the amount of RAM occupied in the InnoDB Buffer Pool. I verified this from a CloudWatch graph, see Free RAM dip right after doing this and the graph stayed that way. The number of free buffer pool pages dropped accordingly.

I am not the only to have seen this phenomenon. Someone once posted a blog entitled "Drain your InnoDB Buffer Pool by moving large fields out of MySQL" who had observed similar Buffer Pool weirdness.

What is interesting is that Bill Karwin had commented on this in a DBA StackExchange post (MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?). He mentioned that BLOBs will occupy InnoDB Buffer Pool Pages. Shrinking them can make data fit in the InnoDB Buffer Pool better could work for you or against you depending on the size of the data.

In your particular case, you only have 269 rows. This should let you know that handling TEXT fields requires some extra I/O in RAM or on disk. You are using MEDIUMTEXT, which can hold up to 16M. Given innodb_page_size = 16384 (16k), a MEDUIMTEXT value can occupy up to 1024 pages. This potential means you are dragging a lot of space from disk based on how much data is in each MEDIUMTEXT field. You may have to live with it.

SUGGESTIONS

Related Question