Re: [Hsqldb-user] Queries on multiple indexes

[Fred T. <fr...@us...>]

If there is an index on field1, it will always be used to filter
the returned rows.

But this does not affect the row count of this query. The temp
table in db.tmp is created when you have set a limit on the
result memory rows, and the row count is above this limit.

However, if you add a LIMIT clause to the query, then you can
force the use of the index on field2 and limit the rows that are
processed.

SELECT something
FROM table
WHERE field1 > x ORDER BY field2 LIMIT 10 USING INDEX

Fred




On Tue, Mar 6, 2012, at 20:47, Sergio Bossa wrote:

  Hi,



I'm using HSQLDB 2.2.8 and I have the following query:



SELECT something

FROM table

WHERE field1 > x ORDER BY field2



Where both field1 and field2 have ad-hoc indexes.

The problem is the query often executes very slowly, creating a
tmp table into db.tmp as if it weren't picking the right index
for the ORDER BY.



Does anyone have similar problems or any feedback about that?



Thanks much,



Sergio B.


--
Sergio Bossa
[1]http://www.linkedin.com/in/sergiob

--------------------------------------------------------------------------
----
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d

_______________________________________________
Hsqldb-user mailing list
Hsq...@li...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

References

1. http://www.linkedin.com/in/sergiob