MySQL – How to Improve Performance for 12 Million Rows Table

mariadbMySQLpartitioningtableview

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!

BIG TABLE

SMALL TABLE

VIEW FROM BIG TABLE LIKE SMALL TABLE

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?

Best Answer

I think I have been using that table. It is clunky when you want to see states/provinces and other things like that.

Sure break out a country if that is all you need. But don't plan on breaking out all ~250 countries into separate tables (plus continents, etc).

VIEWs are not performance enhancers. They can hide the clumsy nature a table like that one. (Especially due to the fcode checks.)

This may help:

INDEX(fcode, country_code)

WHERE feature_code LIKE 'PCL%' AND ...
WHERE feature_code = 'ADM1' AND country_code = 'ES'

If you would care to provide the desired queries (not the views) and the desired table(s), I may be able to provide more suggestions.

Lat/lng searches

Lat/lng needs more work than simply a composite index. Suggest you start with a "bounding box" and these two composite indexes:

INDEX(lat, lng),
INDEX(lng, lat)

If such searches are not fast enough, then look at more complex methods in http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

Related Question