CODEWITHSUNDEEP
mysqlsqlindexing
Krabs With A K
Krabs With A K

Reputation: 11

MySQL: how to speedup a query fetching a large quantity of data and using LIKE

I have a table of 3,666,058 records and 6 columns, defined as follows: 

CREATE TABLE IF NOT EXISTS `annoyance` (
    `a` varchar(50) NOT NULL default '',
    `b` varchar(50) NOT NULL default '',
    `c` longtext,
    `d` varchar(21) NOT NULL,
    `e` float default NULL,
    `f` smallint(6) NOT NULL default '0',
    KEY `ab` (`a`,`b`),
    KEY `b` (`b`),
    KEY `d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I am trying to fetch content of columns a,b, and d, when a starts with a certain prefix (3 letters long), let it be aaa. So I am running the following query: SELECT a,b,c from annoyance where a like 'aaa%';. This should fetch ~1,835,000 records from the table.

My problem is: this query is very slow (when not cached of course), and sometimes takes up to a few minutes.

So, how can I make things faster for this particular query? Simething I tried but without any success is to create an index on a (size 3 or without specifying a size): MySQL would not even bother using the index unless I would force it with FORCE INDEX (index hints) and it did not seem to speedup the query execution.

Upvotes: 1

Views: 493

Answers (3)

Rick James
Rick James

Reputation: 142218

INDEX(a, b, d) (or (a, d, b)) would run faster because it would be a "covering" index.

(Change d to c if c is really what you are fetching.)

Upvotes: 0

Armin
Armin

Reputation: 157

Have you tried LEFT() According to this test it is faster than LIKE. http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex

SELECT a,b,c from annoyance where LEFT(a,3) = 'aaa'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Fetching 1.8 million rows out of 3.6 million basically requires scanning the entire table. There is not much that you can do to improve performance.

Indexes will not help. If you were fetching, say 1000 rows from the table, then indexes can help. And, an index on a would be used for the like. You could also phrase this as:

where a >= 'aaa' and a < 'aab'

If you wanted to make it even easier for the optimizer to choose the index.

Upvotes: 3

Related Questions