get last record in file

Question

I have a table (rather ugly designed, but anyway), which consists only of strings. The worst is that there is a script which adds records time at time. Records will never be deleted.

I believe, that MySQL store records in a random access file, and I can get last or any other record using C language or something, since I know the max length of the record and I can find EOF.

When I do something like "SELECT * FROM table" in MySQL I get all the records in the right order - cause MySQL reads this file from the beginning to the end. I need only the last one(s).

Is there a way to get the LAST record (or records) using MySQL query only, without ORDER BY?

Well, I suppose I've found a solution here, so my current query is

SELECT 
   @i:=@i+1 AS iterator, 
   t.* 
FROM 
   table t,
   (SELECT @i:=0) i 
ORDER BY 
     iterator DESC
LIMIT 5

If there's a better solution, please let me know!

There is no AUTO INCREMENT column - actually, there are only two columns - two strings. If I use ORDER BY I will get orders sorted in alphabetic way, I'm afraid. — shomel, Jun 04 '13 at 14:52
`ORDER BY` will sort according to what you tell it. If you say to `ORDER BY string`, where _string_ is a string column containing a number then it will sort alphabetically. However, in that case `ORDER BY CONV(string, 10, 10)` then it will sort numerically. — nurdglaw, Jun 04 '13 at 15:10
I need to sort them by the order they were added, though there is no information about that in the table — shomel, Jun 04 '13 at 15:50

score 2 · Answer 1 · answered Jun 04 '13 at 14:45

2

The order is not guaranteed unless you use an ORDER BY. It just happens that the records you're getting back are sorted the way need them.

answered Jun 04 '13 at 14:45

Kermit

33,827
13
85
121

Yes, that's not guaranteed, but since it's a random access file and I never delete records - the newest one will always be at the end - therefore I can get them using other methods. But I wonder if there is a way to use MySQL features? – shomel Jun 04 '13 at 15:00
It's pure chance that the order returned is by the physical order. You need to have a column that can be sorted and then you can try something as suggested by Bere. – Kermit Jun 04 '13 at 16:00

score 1 · Answer 2 · answered Jun 04 '13 at 14:46

1

Here is the importance of keys (primary key for example). You can make some modification in your table by adding a primary key column with auto_increment default value. Then you can query

select * from your_table where id =(select max(id) from your_table);

and get the last inserted row.

answered Jun 04 '13 at 14:46

Bere

1,627
2
16
22

Yes, I know the table is ugly designed, but can I do it without changing it? – shomel Jun 04 '13 at 14:58
@shomel once you know your table is ugly and start to feel its 'pinch' that is the right time to modify it :) – Bere Jun 04 '13 at 15:13

get last record in file

2 Answers2