[gdal-dev] Problems with MySQL queries in GDAL

[Even Rouault]

Le jeudi 07 mai 2015 12:05:31, Pavol Hnila a écrit :
> You are right, Even. I made tests today with pure GDAL (1.11)
> command-line tools. Running the "ogrinfo mysql:...tablename" command
> results in all MySQL layers being queried, but only once. So it must be
> the QGIS implementation issue that asks MySQL to query all layers for
> each row, each time, even when zooming in and out. I will contact the
> QGIS folks again.
> 
> Yet - for the GDAL part - I still have two more questions.
> 
> 1) You mentioned that it is expected from the current MySQL driver that
> all the layers are queried at the dataset opening, even if a single
> layer is wanted. Is there a particular advantage in that or did you mean
> with "improving the opening" to eliminate this behaviour?

Yes I meant that querying all the layers is what the MySQL driver currently 
does, as I guess it was easier to implement.
Note that you can specify a tables=a_table;another_table option in the mysql 
connection string that will restrict the number of tables visible&queried to 
the ones you specify

> 
> 2) The current GDAL MySQL driver presupposes the existence of the
> "geometry_columns" table, which is still not standartly implemented in
> MySQL environment. In our MySQL-Database we did not have
> "geometry_columns" table. But even after I have added those table (using
> automatic creation by GDAL when importing a custom shp file with
> ogr2ogr) and adjusted the values in order to correspond with the actual
> tables in the database, I see no change in the behaviour or performance
> of MySQL driver. Why then does the MySQL driver query it?

geometry_columns is specified by the OGC Simple Feature for SQL specification ( 
https://portal.opengeospatial.org/files/?artifact_id=829 ). It contains 
metadata for spatial layers such as the geometry type (point, line, polygon), 
the coordinate dimension (2D vs 3D) and the spatial reference system ID (whose 
definition is then queried in the spatial_ref_sys table). 
It is not intended to improve performance.
The driver could possibly be improved to quietly deal with the absence of 
geometry_columns (in which case, geometry type and spatial reference system 
wouldn't be reported in layer metadata)

> 
> Pavol
> 
> On 06.05.2015 18:54, Even Rouault wrote:
> > Le mercredi 06 mai 2015 18:48:39, Pavol Hnila a écrit :
> >> My impression is that all layers are queried for each record/row in the
> >> table that needs to be shown. If I have 34 rows in my layer and it is a
> >> single layer, there will be 34 queries. If I have 80 tables in the same
> >> database and I want to show those 34 rows of one table/layer, then there
> >> will be 34x80 queries. But why?
> > 
> > I've instrumented the mysql driver to emit a debug trace each time
> > DESCRIBE is issued. The result is that all spatial layers are DESCRIBEd
> > one and only once for each dataset opening.
> > If you see DESCRIBE for a same layer showing several times, then it means
> > that QGIS is opening/closing the OGR dataset many times. Which would be
> > conformant to the issue I noticed some time ago.
> > 
> >> Pavol
> >> 
> >> ---- Even Rouault schrieb ----
> >> 
> >>> Le mercredi 06 mai 2015 18:15:11, Pavol Hnila a écrit :
> >>>> Even,
> >>>> 
> >>>> Thank you very much for your reply. I have actually also tried with
> >>>> OSGeo4W command line tool for Windows 7, using "ogrinfo mysql:...
> >>>> tablename" syntax. The result in mysql log file seems to be the same
> >>>> as with Qgis, so I concluded that the reason must be gdal.
> >>> 
> >>> I guess you just a DESCRIBE for each layer, but not multiple times for
> >>> a given layer ?
> >>> I assumed this was the problem you had (multiple DESCRIBE for a given
> >>> layer) given the logs you showed.
> >>> 
> >>> If your issue is rather that at dataset opening all layers are queried
> >>> with DESCRIBE, then yes this is expected from the current state of the
> >>> mysql driver. That could potentially be improved.
> >>> 
> >>> Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com