Queries on multiple indexes

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Queries on multiple indexes

Sergio Bossa
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.

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Queries on multiple indexes

Fred Toussi-2
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.
------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Queries on multiple indexes

Sergio Bossa
Hi Fred,

thanks for your answer: that certainly makes sense, even if sometimes it seems to avoid the the tmp table creation, but I need to investigate more.
A few related questions:
1) Is the row count calculated after the evaluation of field1 condition?
2) Would an equality condition on field1 (rather than a greater/lesser than) make any difference, maybe via some kind of hash index?

Thanks,

Sergio B.

On Tue, Mar 6, 2012 at 10:24 PM, Fred Toussi <[hidden email]> wrote:
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.
------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user




--
Sergio Bossa
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Queries on multiple indexes

Fred Toussi-2
The simple answer is, the rows returned by your query are limited only by the condition on field1. Therefore only the rows that are returned by the query are added to the result and no row is removed from the result after the ORDER BY is performed. Depending on the values in field1, an equality condition may return a different number of rows.
 
The use of index has no effect on the number of rows added to the result. An equality condition will use an index if there is one.
 
Fred
 
 
On Tue, Mar 6, 2012, at 23:01, Sergio Bossa wrote:
Hi Fred,
 
thanks for your answer: that certainly makes sense, even if sometimes it seems to avoid the the tmp table creation, but I need to investigate more.
A few related questions:
1) Is the row count calculated after the evaluation of field1 condition?
2) Would an equality condition on field1 (rather than a greater/lesser than) make any difference, maybe via some kind of hash index?
 
Thanks,
 
Sergio B.

On Tue, Mar 6, 2012 at 10:24 PM, Fred Toussi <[hidden email]> wrote:
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.
------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
 


 
--
Sergio Bossa
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

 

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user