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.

  • The concept of "first" or "last" is meaningless without an order by. Jul 23, 2014 at 18:33
  • Lets say I have row 1, row 2,row 3. How is row 3 being the last meaningless?
    – tf3193
    Jul 23, 2014 at 18:48
  • Why is row 3 last? Because it happens to be stored last? What happens if someone reorganizes the table so that it is stored first? Never rely on physical ordering of records for the meaning of your data! Jul 23, 2014 at 19:15
  • So this is relevant to my question which I asked? There is always a list of rows it does not matter if the last one is truly the last added row. You are creating an irrelevant argument off the topic of the question.
    – tf3193
    Jul 23, 2014 at 19:33
  • @tf3193 No, he isn't. SQL tables do not have any inherent order. Your previous comment "Lets say I have row 1, row 2,row 3" is not valid in SQL tables. (Unless you have a specific column, say rowID that has these (1,2,3) values stored. If you meant "last as ordered by val5", that's fine. But it wasn't obvious in your comment. Jul 23, 2014 at 21:31

1 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

Give it a Try !!!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.