Tailing database

MySQL & MariaDB Other MySQL® Questions
1

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:

And my query:

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

2
  1. modify query:
[B]Quote:[/B]

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

2. run query as usual 3. fetch data and remember the biggest id 4. put that id into the file 5. modify query again:
[B]Quote:[/B]

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

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:

[B]Quote:[/B]

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;

(put right value as $old_id) Second one should examine less rows, there should by range as type.
3

Oh thank you! I’m using memcache and i think that what you describe can be done easy.