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
