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?

this isn't have anything to do with `longtext` date comparisons can be costly. — Daniel A. White, Jun 05 '13 at 14:47
@DanielA.White indeed but in this case there's only 88k records — Stephan, Jun 05 '13 at 14:49
Sounds like a locking issue. Where are you executing the query? From a language or from the SQL command line? I doubt it has anything to do with the LONGTEXT or date comparison. — robert_difalco, Jun 05 '13 at 14:50
its not just "datetime" field, there are other simple int fields, but the result is same... Only the Primary Key field works in Where query.... — Raheel Hasan, Jun 05 '13 at 15:51

Joachim Isaksson · Answer 1 · 2013-06-05T15:16:21.043

5

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.

edited Jun 05 '13 at 15:16

answered Jun 05 '13 at 14:54

Joachim Isaksson

176,943
25
281
294

its not just added_on, there are other simple fields and output is the same slow... – Raheel Hasan Jun 05 '13 at 15:52
are you suggesting I should change it to `InnoDB`? – Raheel Hasan Jun 05 '13 at 15:53
I thought `InnoDB` was the specialist for `acid transaction`; but for simply one way read, `MyISAM` was the best... – Raheel Hasan Jun 05 '13 at 15:54
@RaheelHasan Because of the whole row read, if you have big rows but only use a few small fields, MyISAM isn't the best unless you index well. If you have a well indexed table for your load so that you avoid full table scans, it may outperform InnoDB but seldom by a huge margin. Fixing the whole row read was [closed as not a bug](http://bugs.mysql.com/bug.php?id=1900#c4097) and has afaik never been opened again. – Joachim Isaksson Jun 05 '13 at 17:14
@RaheelHasan Another solution besides adding indexes for your searches would be to (as suggested in the bug report linked above) put the LONGTEXT along with an ID in a separate table from the one where you need to do the searches. – Joachim Isaksson Jun 05 '13 at 17:16
Hi, actually Im using Solr for indexing....so indexing is not the concern here... – Raheel Hasan Jun 06 '13 at 12:15
ok I converted it into InnoDB, but still no effect... still very very long to return just a few rows – Raheel Hasan Jun 06 '13 at 13:18

score 0 · Answer 2 · answered Jun 05 '13 at 14:51

0

Sounds simple but if possible upgrade your version of MySQL, try increasing the memory limit and max execution time too.

answered Jun 05 '13 at 14:51

Alec.

5,371
5
34
69

MySQL - LONGTEXT field causes hang in SELECT-WHERE statement

2 Answers2