I have performance issues on a query in a big table (12 million records) based on Geonames, that's a read-only database so NO DELETE, UPDATE or INSERT only SELECT.
There are queries I make every now and then filtering by different columns that are not keys (latitude and longitude, fcode and country, only name, etc..), the thing is that with my server resources it takes more than 30 seconds to complete them.
I have made views and small tables (clone of the big table but with only data from one country) to check how to improve it.
With the views, I get similar results than in the big table and using explain I have seen that views check the same amount of rows as the big table (12million rows)
In one of the small tables I get less than 200 milliseconds, more or less depending on the table size.
I'm not a database expert but duplicating data in small tables feels awkward. I'm not sure if that's the best approach that can be done there.
All queries are being sent from my backend, so no stored procedures.
The queries done filtering by primary keys works blazing fast though!.
Thanks in advance for any advice!
UPDATE FOR COMMENTS
- Table definition
CREATE TABLE `geoname` ( `geonameid` INT, `name` VARCHAR(200), `asciiname` VARCHAR(200), `alternatenames` VARCHAR(4000), `latitude` DECIMAL(10,7), `longitude` DECIMAL(10,7), `fclass` VARCHAR(1), `fcode` VARCHAR(10), `country` VARCHAR(2), `cc2` VARCHAR(60), `admin1` VARCHAR(20), `admin2` VARCHAR(80), `admin3` VARCHAR(20), `admin4` VARCHAR(20), `population` INT, `elevation` INT, `gtopo30` INT, `timezone` VARCHAR(40), `moddate` DATE, PRIMARY KEY (geonameid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- What kind of indexes could I add there?