0

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?

  • 1
    I cannot index this. And you can do nothing with it in your server version - 5.6 do not know about calculated fields. The only way I see is to add one more field into table structure with the value of slugify(name) filled using trigger logic and not altered directly. You may use this field value and index the table by it. – Akina Nov 14 '18 at 20:17
1

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.

  • 1
    And if you are working with an older version, it is probably worth it to add slug as a new column. – Rick James Nov 14 '18 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.