MySQL 5.6 – Solving Query Performance Issues When Searching for Slug

MySQLmysql-5.6performancequery-performance

I've a case where I don't know how to better the performance of this query.

This is a simple query to the Geonames.org database.

The query:

SELECT name, latitude, longitude FROM geoname 
WHERE 
slugify(geoname.name) = 'lisboa' 
and geoname.country = 'PT' 
and geoname.fclass in ('A', 'T')

The Explain:

id, select_type,   table,   type,     possible_keys,     key, key_len,    ref,    rows, Extra
 1,      SIMPLE, geoname,    ref,    fclass,country, country,      11,  const,   73462, Using index condition; Using where

I know that the problem is this where clause:

slugify(geoname.name) = 'lisboa'

I cannot index this. The slugify() is a MySQL function that slugify the geoname, for example New York becomes new-york.

What options shoul I consider to have a better performance in this situation. What can I do?

Best Answer

Upgrade to MySQL-5.7 and then MySQL-8.0 (for safety, 1 major version at a time).

Then use Secondary indexes on virtual colums as such:

ALTER TABLE geoname
ADD slug VARCHAR(20) GENERATED ALWAYS AS (slugify(name)),
ADD INDEX slug_country_fclass (slug, country, fclass);

If you wish to stay on 5.6, and you don't change geoname value often, you can denormalise as:

ALTER TABLE geoname
ADD slug VARCHAR(20) NOT NULL DEFAULT '',
ADD INDEX slug_country_fclass (slug, country, fclass);

Populate the column with:

UPDATE geoname SET slug=slugify(name);

After doing one of the above steps, change the query to use the slug generated column as:

SELECT name, latitude, longitude FROM geoname 
WHERE 
slug = 'lisboa' 
and geoname.country = 'PT' 
and geoname.fclass in ('A', 'T')

The new index can then be used for all the criteria of your query.

Related Question