Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Tailing database

jonasljonasl EntrantInactive User Role Beginner
I'm having a table with 1M rows and i'm always fetching the rows that was just inserted (auto_increment) and the queries get slower the more rows i'll insert. Can i query the last 10 "content" without using 'ORDER BY' ?


mysql> describe logfile\G*************************** 1. row *************************** Field: id Type: int(11) Null: NO Key: PRIDefault: NULL Extra: auto_increment*************************** 2. row *************************** Field: content Type: varchar(5000) Null: YES Key:Default: NULL Extra:</pre>

And my query:

mysql> SELECT content FROM logfile ORDER BY id DESC LIMIT 20;</pre>

Comments

  • januzijanuzi Advisor Inactive User Role Beginner
    1. modify query:
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

    SELECT id, content FROM logfile ORDER BY id DESC LIMIT 20;

    </td></tr></table>
    2. run query as usual
    3. fetch data and remember the biggest id
    4. put that id into the file
    5. modify query again:
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

    SELECT id, content FROM logfile where id > $old_id ORDER BY id DESC LIMIT 20;

    </td></tr></table>
    6. You have got old id inside a file, so all You have to do is to get it from file

    In three steps Your script (or application) should:
    - read old id from the file
    - use it in query
    - write last id to the file

    You could check queries:
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">Quote:</td></tr><tr><td class="quote">

    explain SELECT content FROM logfile ORDER BY id DESC LIMIT 20;
    explain SELECT id, content FROM logfile where id > $old_id ORDER BY id DESC LIMIT 20;

    </td></tr></table>
    (put right value as $old_id)
    Second one should examine less rows, there should by range as type.
  • jonasljonasl Entrant Inactive User Role Beginner
    Oh thank you! I'm using memcache and i think that what you describe can be done easy.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.