Re: general design issue
Date: Thu, 23 Apr 2009 19:13:17 GMT
Message-ID: <h53Il.23760$Db2.16910_at_edtnps83>
paul c wrote:
> Bob Badour wrote:
>> paul c wrote: >> >>> skink wrote:
...
> So I tried this:
>
> explain query plan SELECT a,b from text where a GLOB 'a19*'
>
> which returned ""0","0","TABLE text WITH INDEX aindex".
> ...
Another thing I noticed was that even though this statement was used to create the index: CREATE UNIQUE INDEX "aindex" ON "text" ("a" ASC), 'explain' indicated a call to a label called 'sort' when 'select distinct a,b ...' was specified. This is an example of an optimizer ignoring an obvious functional dependency. (admittedly not the case in point - 'sort' wasn't invoked when only the 'a' column name was specified.)
Btw, Sqlite did use an index for 'select a from text where a > 'a190' and a < 'a195'. Lots has been written about algebraic optimal conversions of relational operators but very little about formal conversions of type manipulations like this and many other situations. Too bad, this would have been more worthwhile for the SQL committee to have spent their time on than arguing about keywords and trying to make their informal definiitions precise. .It's pretty bad when a single implementation can't even make consistent optimizations for a single-table query. No slur against sqlite per se, there are things I like about it. I'll bet all the popular sql implementations are similar when it comes to optimizations/separating logical from physical. Received on Thu Apr 23 2009 - 21:13:17 CEST