What query you're trying to optimize? Without the query, it is difficult for people to suggest you anything other than the query analyzer and other similar tools.

Creating indexes is fine. But are they being used? Does the query result in a table scan or an index scan. Have you tried SQL explain?

Xiaotian He

Greenhorn

Posts: 19

posted 2 years ago

It a usual SQL statement with two tables join together. One table has 20 GB data and the other has about 100 KB data.

I created indexes, analyzed table and explained select query. I still got slow response. Anything else should I try?

Xiaotian He

Greenhorn

Posts: 19

posted 2 years ago

How do I know if indexes are used? How do I know if the query results in a table scan or an index scan? Any SQL command is welcome.

Heena Agarwal

Ranch Hand

Posts: 262

4

posted 2 years ago

Xiaotian He wrote:How do I know if indexes are used? How do I know if the query results in a table scan or an index scan? Any SQL command is welcome.

What does the explain output say? I mean you said you have analysed the explain output. We don't have any information about what the explain output said. So perhaps you'd like to share your findings with us.

I have no idea why you are not pasting the query here. You know that you can change the column names and table names while pasting the query. There are sometimes things like the order in which you join columns of two or more tables that can significantly affect the execution plan of a query.

Since I don't have the specifics, I can only give a general answer. In the explain output what does the 'type' column say? Does it say 'all'? Does it say 'index'? Or does it say something else.

I think I may have a few points. But I will get back to you in the later part of the day.

Heena Agarwal

Ranch Hand

Posts: 262

4

posted 2 years ago

It's been a long time since I've worked on MySQL server, so you might want to verify whatever I am going to say.

I think the first ALL with primary and 10 is ok. And it's using where. However you might want to avoid the filesort. See if you can optimize the order by parts so the mysql can use an index instead of having to do another pass for sorting the data.

ref in second row shouldn't be too bad cause the first row lists a small number of rows.

Heena Agarwal

Ranch Hand

Posts: 262

4

posted 2 years ago

I think there can be other points also, but I don't have enough time to revise and go through the explain documentation. Sorry.