mysql querybyattributes

[Frank Warmerdam]

Skalski Artur - askals wrote:
> Can someone point me how to do eficiently querybyattributes on mysql 
> layers using php/mapscript?
>  
> The layer has over 1 000 000 records (I don't want to split it 
> unless...) can be displayed and queried by point when clicking on map, 
> those things work fast but when it comes to querybyattributes I get 
> error message:
> "Execution time of 30 seconds exceded"
>  
> I know that i can set execution time to 'no limit' but it's no use 
> since user will has to wait that long for each query results.
...
> and piece of php code that does the query:
>  
> $qLayer = $gpoMap->GetLayerByName($HTTP_FORM_VARS["SearchedLayer"]);
> $qFiled = $HTTP_FORM_VARS["SearchedField"];
> $qValue = strtoupper($HTTP_FORM_VARS["SearchedValue"]); 
> 
>   $qLayer->queryByAttributes($qFiled, $qValue, MS_MULTIPLE);

Artur,

This is a bit complicated due to the three levels of abstraction.  I
presume currently MapServer is reading all the features and applying
the query itself.  Pretty much a worst case when you have a lot of records.

The next level down is the VRT layer.  When you use an .ovf file, OGR "fakes"
a virtual datasource on top of the lower level non-spatial mysql OGR driver.
We don't want our filters evaluated at this level either.

The bottom level is the OGR mysql driver which knows how to pass attribute
queries on to MySQL as part of a select statement WHERE clause.  Presumably
if we can make this happen things will be fast to do the query.

The trick with OGR connections to pass the filter through to OGR is to use
a query expression starting with WHERE.  So you might do something like:

    expr = 'WHERE ' + $qFiled + ' = ' + $qValue
    $qLayer->queryByAttributes(null,$expr,MS_MULTIPLE);

(forgive my incorrect PHP syntax, I'm just trying to get the idea accross).

Starting a filter with WHERE for the OGR provider tells it that you want it
passed to OGR not evaluated by MapServer.

However, even if you do all this, you are still likely to get poor performance
fetching back the query result features (possibly even getting the wrong
features) if you don't have a primary integer index on the original MySQL
table as OGR needs this for persistent feature ids.

Note, it may be necessary to upgrade to the very latest OGR code to get the
above to work.  I have made a few recent fixes in this area.  Try to use
the GDAL 1.3.2b1 snapshot if you want to pursue this.

Generally speaking, using OGR datasources through the VRT layer and ODBC
has a tendency to problems for queries.  Going to a one pass approach
instead of the current two pass approach would be a big help in this regard.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGF, http://osgeo.org