[MS4W-Users] MS4W 4.0 Render Speeds with GDB, Spatialite, MySQL, and POSTGIS

[IGIS]

In upgrading to MS4W 4.0, we've been exploring how to store data as 
well.  We recently tried some data (~200,000 features in ~50 
layers/tables) and set the data up as (ESRI) GDB, Spatialite, MySQL, and 
PostGIS.

We found PostGIS was fastest at rendering the map images, and I'm 
curious why.  A sample of a simple railroad layer is at the end, and the 
only changes were the CONNECTIONTYPE, CONNECTION, and DATA.

Running shp2img we got results on one of the mapfiles that uses some of 
the bigger layers:

    5.99    mysql
    3.12    sqlite
    2.91    gdb
    2.26    postgis

Is there any reason why PostGIS is fastest, and MySQL is so slow for us?

It's the same hardware and configured per the manual 
<https://mapserver.org/input/vector/> so I was just curious if POSTGIS 
is just faster than OGR, or if there's something I'm missing in the others.

Thanks!

*MAP FILE:*
     LAYER
         NAME 'rail'
         STATUS ON
         TYPE LINE
         CONNECTIONTYPE POSTGIS
         CONNECTION "host=localhost dbname=name user=user password=pass 
port=#"
         DATA "geom FROM geo_rail USING UNIQUE objectid USING srid=3857"
         CLASS
             NAME "Rail Road Tracks"
             STYLE
                 SIZE 1
                 COLOR 0 0 0
             END
             STYLE
                 SYMBOL '|'
                 SIZE 8
                 GAP -10
                 COLOR 92 51 23
             END
         END
     END # layer

This creates an image with a Mapserver URL like: 
/fcgi-bin/mapserv.exe?MAP=/path/to/file.map&SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&FORMAT=png8&TRANSPARENT=true&LAYERS=rail&CRS=EPSG%3A3857&STYLES=&WIDTH=1024&HEIGHT=768&BBOX=COORDS 
<https://giles.interactivegis.com/fcgi-bin/mapserv.exe?MAP=/ms4w/apps/giles/maps/postgis/transportation.map&SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&FORMAT=png8&TRANSPARENT=true&LAYERS=rail&CRS=EPSG%3A3857&STYLES=&WIDTH=2881&HEIGHT=1529&BBOX=-9173685.495930785%2C4372790.404881292%2C-9063577.956685988%2C4431226.51300531>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.ms4w.com/pipermail/ms4w-users/attachments/20190604/b8613b03/attachment.html>