[QGIS-trac] [Quantum GIS] #1555: A more efficient SQL query for uniqueness

[Quantum GIS]

#1555: A more efficient SQL query for uniqueness
-----------------------------------------------+----------------------------
   Reporter:  gjm                              |              Owner:  nobody
       Type:  enhancement                      |             Status:  new   
   Priority:  minor: annoyance or enhancement  |          Milestone:        
  Component:  Data Provider                    |            Version:  HEAD  
   Keywords:                                   |   Platform_version:        
   Platform:  All                              |           Must_fix:  No    
Status_info:  0                                |  
-----------------------------------------------+----------------------------
 The postgres provider, at times, runs a query on a table to see if a
 particular column contains unique values. This is done as part of choosing
 a suitable column to use as an index for that table. The SQL that does
 this uniqueness check is in the uniqueData() function in the
 qgspostgresprovider.cpp file. The SQL is:

 select count(distinct %1)=count(%1) from %2.%3

 where %1 is the column in question, %2 the schema name and %3 the table
 name.

 This counts the number of rows in that row almost twice. A potentially
 more efficient way to achieve the same outcome is with an SQL like this:

 select count(*) from (select %1 from %2.%3 group by %1 having count(*) > 1
 limit 1) as foo;

 This would return 0 or 1, depending if there were unique (or not) data in
 row %1.

 This needs a little bit of testing first to check that it does reduce the
 query time (I don't have the time at the moment).

-- 
Ticket URL: <https://trac.osgeo.org/qgis/ticket/1555>
Quantum GIS <http://qgis.org>
Quantum GIS is an Open Source GIS viewer/editor supporting OGR, PostGIS, and GRASS formats