mysql querybyattributes

[Skalski Artur - askals]

Hi Frank

Many thanks for Your answer, it explains a lot.
I'll give 'WHERE' a try, having PRIMARY INTEGER ID column in my mysql table I hope it will do the trick.

Thanks again for Your advice!

Best Regards

Artur

-----Original Message-----
From: Frank Warmerdam [mailto:fwarmerdam at gmail.com]On Behalf Of Frank
Warmerdam
Sent: Wednesday, April 19, 2006 8:22 PM
To: Skalski Artur - askals
Cc: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] mysql querybyattributes


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
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
****************************************************************************