Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having performance issues on certain database queries that have large possible result sets.

The query in question, I have three ANDs in the WHERE clause

Does the order of the clauses matter?

As in, if I put the ASI_EVENT_TIME clause first (since that would remove the most of the results out of any of the clauses.

Will that improve the run time on the query?

QUERY:

SELECT DISTINCT  activity_seismo_info.* 
FROM `activity_seismo_info` 
WHERE 
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND 
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND 
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND 
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    ) 

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

EXPLAIN of query:

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+ 
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

Using:

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

Propel 1.3

Symfony 1.2.5

share|improve this question
    
The ORDER BY is probably whats taking so long. "Using filesort" can be extremely slow. I have found doing ordering in the application logic a LOT faster than using ORDER BY. –  maclema Mar 22 '11 at 15:27
    
I asked this same question a while back (before this site) on stackoverflow. Check the links for answers I received there. stackoverflow.com/questions/3805863/… –  Scott Mar 22 '11 at 20:02
1  
@maclema - Unless your application is running on a far faster machine than your database, your assertion is certainly untrue, not to mention the pointless burden of all that sorting logic in your application. order by belongs in the database. –  Jack Douglas May 28 '11 at 5:12
add comment

5 Answers 5

up vote 11 down vote accepted

I do not think so. The query optimizer should be clever enough.

You can try rearranging the WHERE clauses and see that EXPLAINS tells you the same in each case.


About what can be done to optimize this query: Is there an index on ASI_EVENT_TIME ? (this is the most crucial I think for this query as you also sort the results using it).

Are there indexes on the other two fields (ASI_SEISMO_ID and ASI_ACTIVITY_ID)?

It would be helpful if you posted the table structure.

share|improve this answer
    
I've never thought to create an index of the event times. I will try that tomorrow on a dev db and see if there is any noticeable difference. –  Patrick Mar 18 '11 at 0:40
    
@Patrick Assuming all other queries that would use this index are ordering this date in descending order, you would want to order the index key (activity_seismo_info.ASI_EVENT_TIME) in descending order as well. –  Matt M Mar 18 '11 at 20:25
    
@MattM I didn't know that you COULD order an index key. Awesome If I do order the index key, will that necessarily hurt performance ordering in the opposite direction to the point that it is worse than no index key? –  Patrick Mar 18 '11 at 20:29
    
@Patrick You are right. My brain is stuck in SQL Server land. You can specify sort order in MYSQL and it will parse, but it is ignored. All indexes are sorted in ascended order in MYSQL. Sorry for the confusion. –  Matt M Mar 18 '11 at 20:35
add comment

From the documentation:

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use an index if the columns do not form a leftmost prefix of the index.

So yes, it should be the same as the order of the columns in a compound index.

share|improve this answer
    
I don't think he's asking that. –  ypercube Mar 17 '11 at 21:48
add comment

ANY field that is used in your WHERE/HAVING clauses and has high selectivity (the number of unique values / the total number of records > 10%~20%) MUST be indexed.

So, if your ASI_EVENT_TIME column has many possible values, first index them all. Then as @ypercube told, try rearranging them and see what EXPLAIN tells you. Should be all around the same.

Additionally, want you to have a look at Indexing SQL LIKE Filters. Though it is not what you need an answer for, but you will still learn about how indexing works under the hood.

*Edit: Refer to the links provided below in the comments to learn more about indexing.

share|improve this answer
8  
-1 Indexing every column is NOT a best practice. Every index costs you in multiple ways. Make sure you pick good indexes, which usually will consist of multiple columns, usually in the order of selectivity and frequency used. This may be SQL Server slanted, but the index info is still valid: sqlskills.com/BLOGS/KIMBERLY/post/…. –  Eric Humphrey - lotsahelp Mar 18 '11 at 13:42
    
@Eric Humphrey +1 For the explanation and the link to Kimberly's site. –  Matt M Mar 18 '11 at 20:19
    
you are wrong, having index on column sometimes hurt your performance on select queries: mysqlperformanceblog.com/2007/08/28/…. You should NEVER use rule of thumb: sometimes it work, sometimes not. –  sumar Mar 19 '11 at 10:52
    
Right, I agree. However, this is valid in case the value selectivity is low. Considering the data type that Patrick (this question author) uses, which is DATETIME, indexing is recommended. Usually this type of field has quite large set of values, unless there is an odd situation when he uses only several possible dates. *I will edit my answer above to make more clear and valid statement. –  Eye Mar 21 '11 at 8:40
add comment

No, it doesn't matter.

The optimizer does a bunch of simple transformations straight after it parses the SQL - this is one of them.

share|improve this answer
add comment

WHERE foo AND bar

optimizes the same as

WHERE bar AND foo

However,

WHERE non-equal#1 AND non-equal#2

Cannot optimize both parts. For example,

WHERE a BETWEEN 1 and 3 AND b > 17

cannot make good use of INDEX(a,b) or INDEX(b,a)

To phrase it differently, any '=' tests AND'd together in the WHERE clause are used first, then one non-'=' (IN, BETWEEN, >, etc) can be handled. No more than one can be effectively optimized.

Your query has 3 such clauses.

As it turns out, INDEX(EVENT_TIME) is probably the most useful -- it will help with one of the ANDs, and it might be used to avoid "filesort" for the ORDER BY.

If there are no duplicate rows (why the heck would there be?), then get rid of DISTINCT. That causes even more effort.

Please provide SHOW CREATE TABLE and SHOW TABLE STATUS when asking performance questions.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.