Slow query, with correlated sub-sub-query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Slow query, with correlated sub-sub-query

E.Pasma
Thanks David for the alernative solution, that is 500 times faster or  
any times faster depending on the number of rows. I hope this does not  
derive attention from my point that the original construction is not  
very well delt with.
It was only a theoretical query derived from a more complex one. The  
atrribute "size" is in this complex query yet an other sub-query,  
counting rows in yet another table. I need to see if the alternative  
solution is still faster there.
E. Pasma
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Slow query, with correlated sub-sub-query

David Raymond
Also of note is that when you make an index on ab (size), your original query, unchanged, becomes about 3 times faster than my modification. I'm not sure on what it looks at to decide if a temporary autoindex is worth it, but in this case it would have been.

When you move back to your more complex version I don't know if an expression index would be practical though, you'll have to play around.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of E.Pasma
Sent: Friday, July 07, 2017 1:12 PM
To: SQLite mailing list
Subject: [sqlite] Slow query, with correlated sub-sub-query

Thanks David for the alernative solution, that is 500 times faster or  
any times faster depending on the number of rows. I hope this does not  
derive attention from my point that the original construction is not  
very well delt with.
It was only a theoretical query derived from a more complex one. The  
atrribute "size" is in this complex query yet an other sub-query,  
counting rows in yet another table. I need to see if the alternative  
solution is still faster there.
E. Pasma
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...