?
Solved

mysql simple ORDER BY optimization

Posted on 2008-10-31
1
Medium Priority
?
470 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 1500 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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