Last row from a query without Order by

Question

I have something like

SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1;

Running this query takes roughly 5 seconds on my database which is far to slow. I was wondering if there was any way to retrieve the last row in a way similar to this

SELECT var1,var2 FROM table where var3=X AND var4=Y limit 1;

bcause that is almost instant (.03 seconds) on my database. So, if there was some way to have a query print everything in reverse order that would solve every issue I am having. I have been searching for a long while now and every answer seems to be with using ORDER BY or subqueries which are all >= 5 seconds.

EDIT The tables are not indexed.

Answer 1

Let's say the table is called mydb.mytable

This query

SELECT VAR1,VAR2 FROM mydb.mytable where var3=X AND var4=Y order by var5 desc limit 1;

can dramatically be improved if you index the table.

There is one of two techniques you can try when indexing the table

Technique #1

ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5);

Within the index, all var5 values are ordered for every var3,var4 tuple. It would simply be a rightmost traversal down the BTREE to get to the last var5. Then, the row is looked up to retrieve var1,var2 from the table.

Technique #2

ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5,var1,var2);

This has the same searchability as INDEX #1, but with an added bonus: var1,var2 are already in the index, so there is no need to go back to the table for a separate retrieval. Making an index that has every column requested in a query is called a covering index.

I have discussed covering indexes before

• Jun 12, 2014 : How to return last used record?
• Aug 06, 2013 : In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index
• Oct 28, 2011 : Using FORCE INDEX
• Aug 15, 2011 : How do you increase MySQL performance using current server resources?

Give it a Try !!!