Applied indexes and analyzed my table, but still got slow query in mySql

[Xiaotian He]

I have applied indexes and analyzed my table, but still got slow query in mySql. Anything else can I do for speeding it up?

[Roger Sterling]

Xiaotian He wrote:I have applied indexes and analyzed my table, but still got slow query in mySql. Anything else can I do for speeding it up?

Have you tired to Improve MySQL Performance: Find and Fix Problem Queries?

[Heena Agarwal]

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]

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]

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]

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.

Perhaps you'd like to refer to this.

[Heena Agarwal]

And perhaps you'd like to define slow. How long does it take?

If I was working on optimizing the said query, I would also require the describe output or the DDL.

[Xiaotian He]

Here are details:

explain select a.whoz_info_id, a.subject, a.price, a.modification_date, a.thumbnail, a.buy_sell, a.owner_dealer, a.int_content_1,a.double_content_1, a.int_content_3, a.division, a.source_url, a.other_hyperlink from whoz_info a, whoz_login b where a.whoz_statecity_id=35 and a.main_category='Automobiles' and a.whoz_login_id=b.whoz_login_id and b.is_locked=0 and a.sub_category='Cars+Trucks' and a.buy_sell=1 and a.flag_click<=3 and a.approved=1 order by a.modification_date desc LIMIT 0, 100

Partial Text
1 SIMPLE b ALL PRIMARY NULL NULL NULL 10 Using where; Using temporary; Using filesort
1 SIMPLE a ref idx_car_total,idx_car_detail,idx_community_friend_... idx_community_friend_total 134 const,const,const,findch2_.b.whoz_login_id 1314 Using where

Full Text
1 SIMPLE a index idx_car_total,idx_car_detail,idx_community_friend_total,idx_community_detail,idx_friend_detail,idx_house_goods_service_total,idx_house_detail,idx_login_all idx_purgeOld_detail 212 NULL 19892 Using where
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 findch2_.a.whoz_login_id 1 Using where

When I say slow, it takes about 5-10 seconds to run one query.

[Roger Sterling]

Xiaotian He wrote:When I say slow, it takes about 5-10 seconds to run one query.

What are your system resources when you run the query - use TOP or similar.

CPU ? I/O ? Memory ?

What is your hardware platform ? Can you upgrade to SSD drives ?

[Xiaotian He]

It is Linux. I don't think I can change the settings or platform.

[Roger Sterling]

Xiaotian He wrote:It is Linux. I don't think I can change the settings or platform.

At least you can investigate it. Use TOP.

http://linux.about.com/od/commands/l/blcmdl1_top.htm

[Xiaotian He]

Heena,

I have not heard from you yet. Any feedback?

[Heena Agarwal]

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

[Heena Agarwal]

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]

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