Solved

mysql simple ORDER BY optimization

Posted on 2008-10-31
1
465 Views
Last Modified: 2012-05-05
I have a very simple SQL:

select * from members
ORDER BY online,photo DESC
LIMIT 0,12
This takes 4 seconds.

and this other sql :
select * from members
ORDER BY online DESC
LIMIT 0,12
takes less than 1 second

I had a look at
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
It seems that you cannot optilmize the sql on 2 keys
Is there another way to optimize this SQL ?
0
Comment
Question by:yarek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
ID: 22851124
well ordering by 1 item is much easier/faster then having to order by 2.

To optimize it I would add an index on the fields you are ordering by, but if it is a text field (DESC) it may not be a good field to add an index to.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question