MySQL - LONGTEXT field causes hang in SELECT-WHERE statement

Question

I am having an issue due to LONGTEXT field. Everything works fine, but when a SELECT query is executed with any WHERE clause in it, the mysql goes to sleep and never returns. There are only 80k records and explicit WHERE comparison works fine. (Some records are over 1M, few are about 700K, but the rest are like only 60 to 100KB).

For example:

[HANGs]
SELECT * FROM item_info 
WHERE added_on > '2013-02-14 19:40:05' AND added_on < '2013-02-15 19:40:05'    
;which is like 2 rows only

[FINE]
SELECT * from item_info
WHERE item_id in (1, 10, 1000)

Is this usual behavior?

Here is the schema:

CREATE TABLE `item_info` (
  `item_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title_md5` varchar(35) NOT NULL,
  `original_document` longtext NOT NULL,
  `added_on` datetime NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `md5_Unique` (`title_md5`)
) ENGINE=MyISAM AUTO_INCREMENT=87781 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

Is there any solution? Or do I have to separate longtext field into a new table with a foreign key?

Answer 1

You're using MyISAM tables. There's a restriction on them that is not present on InnoDB;

MyISAM always reads the complete row from the disk, even if some fields are not queried in the SELECT.

The schema seems like you have an index on item_id but not on added_on.

Since you have no index on added_on, the query will generate a full table scan. A full table scan on a MyISAM table with long rows will need to read a lot of data from disk.

Adding an index on added_on will speed your query up quite a bit, since you'll only have to read the table rows actually needed.