Search code examples
mysqlsqlquery-optimizationdatabase-performance

Slow Query on Medium MySQL Table (1 Million Rows)

I have a MySQL table with around 1 million rows. The table name is dedict When I run a simple query like:

select writtenform from dedict where length(writtenform)>5 
    and partofspeech <> 'abbreviation' order by id asc limit 50,1

the query takes around 1 second.

I have as primary key the id column (autoincrement) and an index on writtenForm.

Below you see the results of explain dedict:

Field               Type                Null    Key     Default     Extra
senseid             varchar(255)        NO      NULL    
writtenForm         varchar(255)        YES     MUL     NULL
languageIdentifier  varchar(255)        YES     NULL        
partOfSpeech        varchar(255)        YES     NULL
_index              int(11)             YES     NULL
writtenText         longtext            YES     NULL
lexiconid           varchar(255)        YES     NULL
id                  int(11)             NO      PRI     NULL        auto_increment

There must be something wrong. I have to run this query 5 times to show a webpage so the load time is more than 5 seconds.

Could you please help me?

Solution

  • Perhaps you can optimize the attribute length. There are 5 attributes of type varchar(255).

    In any case, to get something incredibly faster, you can create a trigger on insert/update statement to store the content length in another attribute. So each time a row is inserted or updated the trigger store the length in another column of type integer.

    Moreover, do you really need to query all the table at a time ?