<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><br><div><div>Hi,</div><div><br></div><div>Actually I'm using</div><div><br></div><div> select * from myTable where recID <> 0 order by creation_dt desc limit 20</div><div><br></div><div>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.</div><div><br></div><div>Everything is working now, thanks to all who replied.</div><div><br></div><div>Jon</div><div><br></div><div><br></div><blockquote type="cite"><div dir="ltr">Have you tried this<div><span style="font-family:arial,sans-serif;font-size:13px">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. </span><br>
</div><div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div style=""><span style="font-family:arial,sans-serif;font-size:13px">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) </span></div>
<div style=""><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div style=""><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Jan 28, 2013 at 7:15 AM, Ivan Smahin <span dir="ltr"><<a href="mailto:ivan_smahin@paradigmasoft.com" target="_blank">ivan_smahin@paradigmasoft.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; position: static; z-index: auto; "><div class="im"><br>
On Jan 28, 2013, at 4:03 PM, jda <<a href="mailto:jda@his.com">jda@his.com</a>> wrote:<br>
<br>
><br>
> On Jan 28, 2013, at 8:58 AM, Ivan Smahin <<a href="mailto:ivan_smahin@paradigmasoft.com">ivan_smahin@paradigmasoft.com</a>> wrote:<br>
><br>
>><br>
>> On Jan 28, 2013, at 3:33 PM, jda <<a href="mailto:jda@his.com">jda@his.com</a>> wrote:<br>
>><br>
>>><br>
>>> 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:<br>
>>><br>
>>><br>
>>> SELECT * FROM mytable WHERE id > (select max(id) - 20 from mytable)<br>
>>><br>
>>><br>
>>> It works, but is very slow. With a database of ~7000 records it takes ~20 seconds.<br>
>>><br>
>>> Is there a way to optimize this (using V4.9.1, Mac OS X).<br>
>>><br>
>><br>
>><br>
>> Thinking more of this ...<br>
>> Is ID field indexed?<br>
>> Could you send me that db to test it here?<br>
>><br>
><br>
> Hi,<br>
><br>
> id is a VLong, not indexed.<br>
><br>
> Would you still like the database?<br>
><br>
<br>
<br>
</div>I wish to test it here anyway.<br>
I'm sure that indexing improves performance a lot.<br>
But could you show your tricks with RecIDs as well?<br>
<div class="im HOEnZb"><br>
<br>
--<br>
Best regards,<br>
Ivan Smahin<br>
Senior Software Engineer<br>
Paradigma Software, Inc<br>
Valentina - The Ultra-Fast Database<br>
<a href="http://www.valentina-db.com/" target="_blank">http://www.valentina-db.com</a><br>
<br>
</div><div class="HOEnZb"><div class="h5">_______________________________________________<br>
Valentina mailing list<br>
<a href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br>
<a href="http://lists.macserve.net/mailman/listinfo/valentina" target="_blank">http://lists.macserve.net/mailman/listinfo/valentina</a><br>
</div></div></blockquote></div><br></div>
_______________________________________________<br>Valentina mailing list<br><a href="mailto:Valentina@lists.macserve.net">Valentina@lists.macserve.net</a><br>http://lists.macserve.net/mailman/listinfo/valentina<br></blockquote></div><br></body></html>