optimising a query

Question

i have a table in database that is having 7k plus records. I have a query that searches for a particular id in that table.(id is auto-incremented)

query is like this->

select id,date_time from table order by date_time DESC;

this query will do all search on that 7k + data.......isn't there anyways with which i can optimize this query so that search is made only on 500 or 1000 records....as these records will increase day by day and my query will become heavier and heavier.Any suggestions?????

Answer 1

I don't know if im missing something here but what's wrong with:

select id,date_time from table where id=?id order by date_time DESC;

and ?id is the number of the id you are searching for...

And of course id should be a primary index.

Answer 2

If id is unique (possibly your primary key), then you don't need to search by date_time and you're guaranteed to only get back at most one row.

SELECT id, date_time FROM table WHERE id=<value>;

If id is not unique, then you still use the same query but need to look at indexes, other contraints, and/or caching outside the database, if the query becomes too slow.