CODEWITHSUNDEEP
mysql
sveti petar
sveti petar

Reputation: 3797

How to speed up MySQL query involving a "like" condition on a varchar column?

I have a table with a varchar column that contains stock market tickers. The values can have the stock exchange prepended to the actual ticker, but some don't. For example:

NYSE:MSFT
NYSE:AAPL
AMT:STZ
^SPX
JMAT

These are all valid tickers. Now, when someone searches for a ticker, then the query looks something like:

select * from stocks where ticker like '%'.$string.'%'

The table, in production, will have over 100k entries. I want to get ahead of that potential problem by indexing on the column, but it appears based on MySQL docs that it is not possible, not for a query like this anyway.

The docs state that a varchar index is possible, but in a like query it won't work if there's a wildcard before the search string. And I do need a wildcard before the search string.

Is there another option to speeding up this query?

Upvotes: 0

Views: 2826

Answers (2)

The Impaler
The Impaler

Reputation: 48865

If you want to speed up the query you'll need to avoid matching with patterns that start with a %. To do this you'll need to add some redundancy to keep the second value in a separate column.

Therefore, your table should look like:

full_ticker  ticker
-----------  ------
NYSE:MSFT    MSFT
NYSE:AAPL    AAPL
AMT:STZ      STZ
^SPX         ^SPX
JMAT         JMAT

Simple string manipulation will produce the second (new) column; for 100k rows this will take no time.

Now you can create two indexes, one per column:

create index ix1 on stocks (full_ticker);

create index ix2 on stocks (ticker);

Finally, when a search comes in with a colon, then use the first index:

select * from stocks where full_ticker like $string.'%'

If a search comes in without a colon, then use the second index:

select * from stocks where ticker like $string.'%'

Notice that both searches avoid using the % at the beginning of the pattern. This way, your queries will use the indexes and will be blazing fast.

A final note: Another option is to create the second, redundant column as a "virtual column"; virtual columns can actually be indexed, to produce the same experience I detailed here. Just a though.

Upvotes: 3

Serkus
Serkus

Reputation: 31

Like you said, there is no possibility to use index in combination with using '%wildcard%'. There is possibility to use feature called: Full Text Searching (FTS), that can improve your searching performence in comparison with using LIKE operator.

Some example performance analysis is here

There is a lot of discussions about FTS, you can read on StackOverflow ofcourse.

Upvotes: 1

Related Questions