> The query return the apropiate values as always the id -> id2 relation is > the same and id -> name and id2 -> name2.
So your id maps uniquely to id2, name and name2. But what about year? What value of year do you want to be used in sorting? Anyway try to change query as "GROUP BY id, name2, year". If your id maps uniquely to the year it won't affect results and along with Simon's suggestion it should speed up your query. If your id doesn't map uniquely to year then you are sorting by random number, so you better remove that from ORDER BY clause. Pavel On Tue, Jun 29, 2010 at 7:02 PM, J. Rios <jriosli...@gmail.com> wrote: > The query return the apropiate values as always the id -> id2 relation is > the same and id -> name and id2 -> name2. I keep them in the same table to > speed up other queries that are now very quick as uses indexes for the > ordering but in this SELECT the GROUP BY makes the difference and the SORT > is getting slow as its not using the index. I have read that sqlite only > uses one Index by query. > There must be a solution but I dont get it. > > Thanks in advance > > On Tue, Jun 29, 2010 at 9:49 AM, Pavel Ivanov <paiva...@gmail.com> wrote: > >> > SELECT id, name, id2, name2 GROUP BY id ORDER BY name2, year DESC LIMIT >> 0, >> > 15 >> > >> > How can I make it faster? >> >> First of all your query should return nonsense in any field except id. >> I bet it will also return different results (for the same ids) >> depending on what LIMIT clause you add or don't add it at all. >> >> But to make this particular query faster you should have an index on >> (name2, year). Note: index on both fields, not 2 different indexes on >> each field. >> >> >> Pavel >> >> On Tue, Jun 29, 2010 at 2:24 AM, J. Rios <jriosli...@gmail.com> wrote: >> > I have the next table >> > >> > table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year >> INTEGER >> > ); >> > >> > I have created the next indexes : index1( name ), index2( id2 ), index3( >> > name2 ); >> > >> > The database have about 200,000 records. >> > The next query takes about 2 seconds and I think its too much. >> > >> > SELECT id, name, id2, name2 GROUP BY id ORDER BY name2, year DESC LIMIT >> 0, >> > 15 >> > >> > If I remove the sorting condition the query is instantaneous. >> > >> > How can I make it faster? >> > >> > Thanks in advance >> > J.Rios >> > _______________________________________________ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
