Question about search speed

[jda]

Hi,

Actually I'm using

     select * from myTable where recID <> 0 order by creation_dt desc limit 20

and it is extremely fast. I'm adding the WHERE option for reasons peculiar to the needs of my app's UI, not for programmatic reasons.

Everything is working now, thanks to all who replied.

Jon


> Have you tried this
> select * from myTable order by creation_dt DESC limit 20 but with the rowid instead?  (as mentioned above but with rowid) Or using your id field, though there can be problems with that method depending on how things are set up. If your database is recycling unused (IE deleted) id numbers then you can't guarantee that last insert will be highest number. 
> 
> Might be faster. The other way (20 - blah blah) it has to do the math on every single record and compile a list of matching records.  Just selecting everything, sorting by the recid (desc) then grabbing the first 20 should be considerably faster. An index on the field in question would help too. (not sure if rowid is automatically indexed or not) 
> 
> 
> 
> 
> On Mon, Jan 28, 2013 at 7:15 AM, Ivan Smahin <ivan_smahin at paradigmasoft.com> wrote:
> 
> On Jan 28, 2013, at 4:03 PM, jda <jda at his.com> wrote:
> 
> >
> > On Jan 28, 2013, at 8:58 AM, Ivan Smahin <ivan_smahin at paradigmasoft.com> wrote:
> >
> >>
> >> On Jan 28, 2013, at 3:33 PM, jda <jda at his.com> wrote:
> >>
> >>>
> >>> I have a built-in search that finds the last 20 records entered. Each record has a unique sequential number (1..N) in the field "id", and this is the query:
> >>>
> >>>
> >>>   SELECT * FROM mytable WHERE id > (select max(id) - 20 from mytable)
> >>>
> >>>
> >>> It works, but is very slow. With a database of ~7000 records it takes ~20 seconds.
> >>>
> >>> Is there a way to optimize this (using V4.9.1, Mac OS X).
> >>>
> >>
> >>
> >> Thinking more of this ...
> >> Is ID field indexed?
> >> Could you send me that db to test it here?
> >>
> >
> > Hi,
> >
> > id is a VLong, not indexed.
> >
> > Would you still like the database?
> >
> 
> 
> I wish to test it here anyway.
> I'm sure that indexing improves performance a lot.
> But could you show your tricks with RecIDs as well?
> 
> 
> --
> Best regards,
> Ivan Smahin
> Senior Software Engineer
> Paradigma Software, Inc
> Valentina - The Ultra-Fast Database
> http://www.valentina-db.com
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20130128/77b2c736/attachment.html>