Indexes - do you need the field in the ORDER BY?

[youradds]

Hi,

Just a question really

I'm working with quite a large amount of data (500,000 test clicks at the moment- but it could go as high as 2million, and maybe ever more)

I have an example query:

SELECT * FROM Clicks WHERE field = 1234 AND TheDate = 20101101 ORDER BY click_id

So, I currently have an index setup with "field", "TheDate" and "click_id"

My question - do I need the "click_id" in the index, or will it be just as quick without? I did a few tests - and it takes about 2.1 seconds to run ... which doesn't seem to bad to me?

TIA

Andy

 

[r937]

you have a single, 3-column index?

no, the ORDER BY cannot use that index

so you could make the index a bit more efficient by removing click_id from the index

also, depending on how many rows you expect [red]field = 1234 AND TheDate = 20101101[/red] to return, you can ~really~ speed things up by omitting the ORDER BY clause and sorting the results in your application

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Hi,

Thanks for the reply.

Will give that a go (we still need the ORDER BY though, as we are going to be showing the rows in reverse order (newests, to oldest)

Thanks again

Andy

 

[ingresman]

Accept you need the order by but a sort in the app could do that as well. I suppose you have to weigh up where it it best done e.g. which server (i.e. db or web) has the most spare capacity, how food your sort package is on the web server etc.
I just wanted to pile in really. In MS SQLServer you start to get rows in the app before the query has finished if you don't have an order clause (known as a fire hose cursor). If you order by on the SQL it will have to finish the sort before you get the rows, making you app look less responsive.
I can't recall if MYSQL does this or not.