My SQL reverse selection order

[TheHiveMind]

I am looking for a single record in a big table. It is indexed on rather useless columns.
"Oder by" is slower than just going through all of them. What I want it to do is to select the records the way it does when I don't specify any order, except, I want it in reverse. It mustn't order anything after selection, it must pick them up that way. I am trying to get it to search the newest records first so that it can break out of the query as soon as it retrieves a result. Surely something like this is possible, it's an efficient solution to an annoying little problem.

Something like: ReverseSelect xyz from Table1 where abc = 'blahblah' limit 1;

 

[crazy_cat]

http://is.gd/8usN9

Just a weird thought - Use the above link as a reference. So first get all the row numbers and then use CTE's and recursion to select from a subset of rows (probably the ones with high row numbers) to find you answer..

 

[FarligOpptreden]

The OP asked for a MySQL statement... AFAIK MySQL doesn't have CTEs. I think all database engines should support CTEs. They're awesome!

 

[hof]

Have you run an EXPLAIN on the query to see what's MySQL's optimizer is doing? I'd suggest doing this first, it's probably just not hitting an index on the ORDER BY clause, so is using a temporary table and a filesort which is very inneficient. Look at the EXPLAIN output for more info... you may just need to use a FORCE INDEX on the ORDER BY column.

If that isn't the case, are you using an auto-increment PK in the table? If so, larger PK's are obviously going to be newer records. So create a new index on the PK field, only order it DESC. Then force your query to use the index and apply the limit:

CREATE UNIQUE INDEX descPK ON Table1 (pkField) DESC

Then,

SELECT ...
FROM Table1
FORCE INDEX (descPK)

 

[guest2013-1]

I'd highly recommend ALWAYS adding the following columns to any table:

Active (bit)
Created_Date (datetime)
Updated_Date (datetime)
Created_UserID (int)
Updated_UserID (int)

 

[Moederloos]

Sort order on an unsorted field is not guaranteed.
In other words, a new record could be anywhere.
If you have a clustered index built the new record will be positioned according to the position of the new record in the clustered field order.

I agree with AcidRazor. Always have some sort of mandatory field(s) common to all tables. I use a surrogate primary key such as TableID int which is a incremental db controlled row number.

Depending on how often you want to run this command, you can try:
Build an index on the required field: Do this if you are going to run it often
From the command line (on linux): echo "select * from tablename;" | mysql -uuser -ppassword DBName | tail -n1: gets the last record. Of course, as mentioned, the order may not be what you expect.

And if nothing here works, do a DESCRIBE tablename and a SHOW CREATE TABLE tablename, and post it here so we can see. Maybe something else will come to light.

 

[guest2013-1]

Point is Database Design is the key-foundation to any application. If you can't compensate for possible changes to the application using a well-grounded database as a foundation, you will run into problems like these where you'll go "OMF, it would have been so much easier if I had x in there!"

Now instead of going back and changing things in the database side (which sometimes, with data already running, is not very possible to do and you'll lose historical value), you'd spend hours on hours just to try and get something to work, which would've worked out of the box (so to speak) if the db was done correctly. Now I do know that db's can be inherited, but then it's up to you to tell the client (or your boss) that what they want would be easier if there's a database design change (for xyz) and that any data going forward would then be correctly displayed in the report they want.

Instead of changing the database as the code changes, you need to learn to be able to design a database to work as a foundation so that the code you implement to interact with it is piss easy

 

[hof]

I don't see how you can infer bad database design from the post - no schema has been given. Indexes frequently need to be changed and optimized as the amount of data and sparsity changes within the database. This just sounds like an index problem to me. MySQL's optimizer often needs help when it comes to GROUP BY and ORDER BY statements when used in conjunction with WHERE clauses. The optimizer is almost certainly using an index on your WHERE clause and not on the ORDER BY clause - hence it's quicker to read through all results than get an ordered result. My post above should fix the problem.

 

[guest2013-1]

no schema has been given

exactly

 

[Moederloos]

Just be careful of unique indexes though if you often do restore testing etc. A unique index can severely degrade the restore process time.