Leaflet geoJson and Mysql, how to optimize an extremely slow query

Question

I need to create a geoJson polygon layer, the data is in a mysql db and it refers to regions of a country. Specifically I need to count the number of objects in each region. My map shows the regions colored according to the number of artifacts found, sort of like a coropleth. The objects are in a table with foreign key to the regions table. The web application and the db are on different servers and this definitely worsens the performance

I tried a query like this:

select c.name, st_asgeojson(c.geom) geometry, count(a.*) tot 
from county c 
inner join artifact a on a.county = c.id 
group by c.name

The query gave me an error because I did not enter geometry in the group by, but if I group by geometry as well the db goes "out of memory". I rewrote the query using the "with" construct:

with 
  props AS (select county, count(*) tot from artifact group by county), 
  geom as (select id, name, st_asgeojson(geom) geometry from county) 
select geom.id, geom.name, geom.geometry, props.tot 
from props 
inner join geom on props.county = geom.id;

it works but it is extremely slow, almost 10 seconds for about ten polygons...I tried explain analyze but there seems to be no problem, also because all field are indexed:

I've always used Postgresql, which I find much more robust when working with geographic data, in this case however I have to use Mysql, so I'm asking if there is a smarter way to optimize the query to get a collection of geoJson to be managed with Leaflet

Answer 1

"all field are indexed" -- This is usually a bad sign. Read about "composite" indexes.

The first query can be rewritten as

select  c.name,
        st_asgeojson(c.geom) AS 'geometry',
        ( SELECT count(*) FROM artifact
             WHERE county = c.id ) AS  tot
    from  county c;

That may run faster and avoid the memory problem. Try it.

Here's another way:

SELECT  c.name,
        st_asgeojson(c.geom) AS 'geometry',
        a.tot
     FROM ( SELECT county, COUNT(*) AS tot
              FROM artifact
              GROUP BY county ) AS a
     JOIN county AS c  ON a.county = c.id;

That will probably do the grouping and grouping before walking through the geometry.