optimize 20 second query

MySQL & MariaDB Other MySQL® Questions
1

[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?!

2

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.

3
[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?

4
[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);

5

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

6

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.