# optimize 20 second query **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 12, 2007, 11:38am UTC](https://forums.percona.com/t/optimize-20-second-query/521 "2007-11-12T11:38:17Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![alex\_time](https://avatars.discourse-cdn.com/v4/letter/a/9e8a1a/32.png) [@alex\_time](https://forums.percona.com/u/alex_time)\ **Post date:** [November 12, 2007, 11:38am UTC](https://forums.percona.com/t/optimize-20-second-query/521/1 "2007-11-12T11:38:17Z")
[mysql 5] I have a simple table with some fields, one of theme is a mediumblob field. 12.000 rows about 422 Mb. I just want to do a simple query like this SELECT \* FROM tbl1 WHERE condition ORDER BY field1 DESC; This query need 20 seconds to be done! If I avoid condition AND “Order By” or if I use a LIMIT 0,1, than the query take almost 1 sec, but I really need to select more than 1 record and use condition, order by and also join it with another table. How can be optimized this simply query?! ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [November 12, 2007, 8:13pm UTC](https://forums.percona.com/t/optimize-20-second-query/521/2 "2007-11-12T20:13:40Z")
It depends on your WHERE condition. But if your looks something like this: …WHERE col1 = ‘something’ AND col2 = 'something else’ORDER BY col3 Then you create a combined index on (col1, col2, col3). Note how all the columns of the WHERE condition is part of the index and that the order by is the last column in this index. Same principal if you have a join condition. ---
**Author:** ![alex\_time](https://avatars.discourse-cdn.com/v4/letter/a/9e8a1a/32.png) [@alex\_time](https://forums.percona.com/u/alex_time)\ **Post date:** [November 13, 2007, 3:35am UTC](https://forums.percona.com/t/optimize-20-second-query/521/3 "2007-11-13T03:35:24Z")
| [B]sterin wrote on Mon, 12 November 2007 20:43[/B] | | It depends on your WHERE condition. But if your looks something like this: …WHERE col1 = ‘something’ AND col2 = 'something else’ORDER BY col3 Then you create a combined index on (col1, col2, col3). Note how all the columns of the WHERE condition is part of the index and that the order by is the last column in this index. Same principal if you have a join condition. | So I need to create index for each columns I use in WHERE and ORDER BY, is this what you mean? ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [November 13, 2007, 11:17am UTC](https://forums.percona.com/t/optimize-20-second-query/521/4 "2007-11-13T11:17:42Z")
| [B]Quote:[/B] | | So I need to create index for each columns I use in WHERE and ORDER BY, is this what you mean? | No, you need to create \_one\_ index that contains all these columns. This example creates one combined index based on (column1, column2, column3) CREATE INDEX … (column1, column2, column3); ---
**Author:** ![SupperWare](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@SupperWare](https://forums.percona.com/u/SupperWare)\ **Post date:** [February 2, 2008, 8:03am UTC](https://forums.percona.com/t/optimize-20-second-query/521/5 "2008-02-02T08:03:50Z")
What is the differenc between seperate indexes and combined indexes? And in this thread there is a ORDER BY col3. So is tehre ny need to be index of col3 seperate? or is combined indexing enough? Best Regards, Supper ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [February 2, 2008, 10:49am UTC](https://forums.percona.com/t/optimize-20-second-query/521/6 "2008-02-02T10:49:58Z")
The difference is that a combined index contains several columns which means that it is easier to find a certain record when you have more than one condition. Think about it this way. If you have a phone book that is only ordered by surname you will get: Doe, Richard Doe, Anna Doe, John Doe, Sarah … And finding Doe, John in that data means that you will have to go through all the Doe’s to check which given name matches. But if you create a combined index with (surname, given\_name) it is much easier to find Doe, John since you know that he will be placed in alphabetical order after Doe, Anna and before Doe, Richard. No separate index of the col3 is needed since it is only used in the ORDER BY. And by placing it last in the combined index it means that MySQL can use the index to also retrieve the rows in sorted order and avoid a later sort of the matching rows.