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.
SELECT name, latitude, longitude FROM geoname WHERE slugify(geoname.name) = 'lisboa' and geoname.country = 'PT' and geoname.fclass in ('A', 'T')
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?