Select query is very slow on my DB table containing a BLOB field

Please, how can I improve the speed of my select query with BLOB field. See table structure below;

CREATE TABLE IF NOT EXISTS `mydb`.`question_` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`question_text` BLOB(90000) NOT NULL,
`comprehension_id` INT UNSIGNED NOT NULL DEFAULT '0',
`MCQ` TINYINT(1) NOT NULL DEFAULT '0',
`subject__id` INT UNSIGNED NOT NULL,
`col1` INT NULL,
`col2` INT NULL,
`col3` INT NULL,
`col4` INT NULL,
`col5` INT NULL,
`col6` INT NULL,
`col7` INT NULL,
PRIMARY KEY (`id`),
INDEX `fk_question__subject_1_idx` (`subject__id` ASC),
CONSTRAINT `fk_question__subject_1`
FOREIGN KEY (`subject__id`)
REFERENCES `mydb`.`subject_` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;

I need the BLOB field so that column 'question_text' can accommodate images, I understand I could be better-off with the alternative of storing the image file link to DB and saving the image file to disk, but I'm not doing so because the nature of my app is peculiar.

Using BLOB field is the preferred way for my app because my users will regularly create and update 'questions', and my users who aren't so savvy with the technicalities of image upload, image URL etc, will rather copy the image wherever it is and paste into a rich text box (e.g. CKEditor) provided by my app, and continue with typing - just like with MS-Word. CKEditor can accept mixtures text, images, tables, etc. The entire content of this rich text box is written to DB (that is; the 'question_text' column). Hence the need of a BLOB field.

But the challenge I'm facing here is that the question page takes a longer time to load when managing questions at the back-end.

How can I improve the speed, or what should I have done to improve the speed.

My app is built on javaee stack (JPA, EJB, JSP, Servlet, Glassfish) and below is the select query i'm using to display the list of questions...

SELECT  q.id, CONVERT(q.question_text USING UTF8), q.subject__id,
        q.comprehension_id, q.MCQ
    FROM  question_ q
    WHERE  q.subject__id = 13
    limit  1, 20

You can also view Question.java code which is mapped to question_table via ORM on https://www.dropbox.com/s/on2wv92c71owx2d/Question.java?dl=0

Thanks,

Solution

Thanks for all your efforts.

The faults weren't really from the blob field but from the rich text editor (CKEditor) displaying the content of the blob field. I realise that CKEditor is a heavy javascript library (over 1000 line in compressed mode and over 22000 lines in uncompressed mode) and I failed to ajax and lazily load it when displaying each question (as well as the options/choices under each question).

Let's say I want the page to display 100 questions and each question contains at least 4 options/choices, that will mean 100x4 (400) calls to CKEditor.

So I've resulted to rework my code to load CKeditor only when needed and that way the content in the editor which is gotten from the blob field also gets to load only when needed