Question about search speed

[Mike Bonner]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20130128/add63565/attachment.html>