SQL sorting optimisation

[Chris]

"SELECT .......... WHERE .... ORDER BY a DESC, b DESC"

b is indexed
a is not indexed
neither a nor b are in WHERE (no connection, where uses different criterias that are not indexed)


I get quite a lot of "Sorting..." processes when checking SQL processes list.
Should I put an index on 'a'? Would it improve the sorting speed?

[Scion]

depends on the data in column a....whats its cardinality like....(lots of different values, or lots of similar values)

if you have lots of different values then an index should help speed up the select query time.

out of interest why no indexes on the columns used in the where clause?

[Chris]

I don't know if I read this correct, but from my SQL process list I get "Sorting..." state frequently. Does this mean the time is spent on ORDER BY part or on WHERE+ORDER BY?

In short, I haven't put indexes on where part becase my problem is sorting, so I want to fix it first and compare the efficiency.

So, does indexes speed up sorting? (I know this will speed up the whole select statement, just wonder about the ORDER BY part if this makes sense optimizing it)

[nicmo]

index is a kind of internal sorting by itself so yes, it will help

[Chris]

Any other info about sorting performance optimisation? Any sites/articles about this topic?

[Scion]

Im not really a MySQL expert....probably not too many of them around here...thats a question for the MySql specific forums

It may be that you cannot necesarilly acredit the sorting activity your seeing to the order by part of that query (even though it seems reasonable...and is probably what i would assume too )

What i would do is create a small test bed...

create a set of tables that match the ones used in the querry...fill them up with a bunch of data to the same level as youve got...and then play around with adding the indexes to see if they speed things up...then do the same tests again with double that data....and again with what you feel is a reasonable upper limit, You may also want to include in your tests the writing of data to those tables as well since adding indexes will add a hit to the write speed.

Nothing so good for helping make an informed decision as cold hard facts

oh and id ask on the MySql forums too...

[codestryke]

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

If this was MSSQL or Oracle I would recommend a view of the data. I haven't investigated mySQL's views yet so I really don't know the pros or cons of using them. I say give them a shot and monitor the performance and see what happens, can't hurt to bad

[Chris]

Im not really a MySQL expert....probably not too many of them around here...thats a question for the MySql specific forums

As they say "I know you sux, but I sux even more" As for mysql specific forums 90% of posts there is about "how to connect to the database" or similar type... But if you know of any decent mysql forum drop a link.

As for creating heavy tests, I don't have the budget for that I need to go with something less time consuming, even if it is not the best solution.

I was thinking, the "Sorting results..." in processlist is probably nor ORDER BY but everything connected to indexes, or to be more precise, it shows up when filesort method is being used (which is called by both WHERE and ORDER BY). What do you think?

Seems I can go two ways:
1) rearrange indexes so no filesort is used.
2) increase buffers sizes (I have some spare memory) (how do you change buffers sizes, anyone did it, is it effective?).

As for my database I have these in red: Handler_read_rnd, Handler_read_rnd_next, Sort_merge_passes, Opened_tables, Table_locks_waited. What is the best method of optimizing these?

[Scion]

ok...ill correct myself... Im NOT a MySQL expert....

but here is a couple of resources that ive stumbled across before.

http://www.mysqlperformanceblog.com/  this is the blog from the athors of the High Performance MySQL Book

and they have a performance oriented forum here: http://forum.percona.com/index.php/f/2/

if that helps any.

[Greyth]

re:OP

Quick suggestion: Try it both ways and Time it with the system timer.

Technically I suppose this is why they make code profiling, but I've found that on a small scale a simple roll-your-own timer placed before and after your query can provide huge amount of insight into what code works best in whatever scenario.