CODEWITHSUNDEEP
mysqlperformance
armandomiani
armandomiani

Reputation: 730

How is it possible to have a good EXPLAIN and a slow query?

How is it possible to have a good plan in EXPLAIN like below and have a slow query. With few rows, using index, no filesort.

The query is running in 9s. The main table has around 500k rows.

When I had 250k rows in that table, the query was running in < 1s.

Suggestions plz?

explain

Query (1. fields commented can be enabled according user choice. 2. Without FORCE INDEX I got 14s. 3. SQL_NO_CACHE I use to prevent false results):

    SELECT SQL_NO_CACHE
          p.property_id
        , lct.loc_city_name_pt
        , lc.loc_community_name_pt
        , lc.loc_community_image_num_default
        , lc.loc_community_gmap_longitude
        , lc.loc_community_gmap_latitude
    FROM property as p FORCE INDEX (ix_order_by_perf)
    INNER JOIN loc_community lc 
        ON lc.loc_community_id = p.property_loc_community_id
    INNER JOIN loc_city lct FORCE INDEX (loc_city_id)
        ON lct.loc_city_id = lc.loc_community_loc_city_id
    INNER JOIN property_attribute pa
        ON pa.property_attribute_property_id = p.property_id        
    WHERE p.property_published = 1
        AND (p.property_property_type_id = '1' AND p.property_property_type_sale_id = '1') 
        AND p.property_property_housing_id = '1' 
--      AND p.property_loc_community_id = '36'  
--      AND p.property_bedroom_id = '2'
--      AND p.property_price >= '50000' AND p.property_price <= '150000'
--      AND lct.loc_city_id = '1'
--      AND p.property_loc_subcommunity_id IN(7,8,12) 
    ORDER BY 
          p.property_featured DESC
        , p.property_ranking_date DESC
        , p.property_ranking_total DESC
    LIMIT 0, 15

Query Profile

query profile

The resultset always outputs 15 rows. But the table property and property_attribute has around 500k rows.

Thanks all,

Armando Miani

Upvotes: 3

Views: 155

Answers (4)

Marcus Adams
Marcus Adams

Reputation: 53830

This really seems to be an odditity in EXPLAIN in this case. This doesn't occur on MySQL 4.x, but it does on MySQL 5.x.

What MySQL is really showing you is that MySQL is trying to use the forced index ix_order_by_perf for sorting the rows, and it's showing you 15 rows because you have LIMIT 15.

However, the WHERE clause is still scanning all 500K rows since it can't utilize an index for the criteria in your WHERE clause. If it were able to use the index for finding the required rows, you would see the forced index listed in the 'possible_keys' field.

You can prove this by keeping the FORCE INDEX clause and removing the ORDER BY clause. You'll see that MySQL now won't use any indexes, even the one you're forcing (because the index doesn't work for this purpose).

Try adding property_property_type_id, property_property_type_sale_id, property_property_housing_id, and any other columns that you refer to in your WHERE clause to the beginning of the index.

Upvotes: 2

Kevin Bedell
Kevin Bedell

Reputation: 13404

Based on the output of your explain the query, here are my initial thoughts:

This portion of your query (rewritten to excluded the unneeded parentheses):

    p.property_published = 1
    AND p.property_property_type_id = '1' 
    AND p.property_property_type_sale_id = '1'
    AND p.property_property_housing_id = '1'

Put conditions so many conditions on the property table that it's unlikely any index you have can be used. Unless you have a single index that has all four of those attributes in it, you're forcing a full table scan on the query just to find the rows that meet those conditions (though, it's possible if you have an index on one of the attributes it could use that).

First, I'd add the following index (have not checked this for syntax errors):

CREATE INDEX property_published_type_sale_housing_idx  
ON property (property_published, 
             property_property_type_id, 
             property_property_type_sale_id, 
             property_property_housing_id );

Then I'd re-run your EXPLAIN to see if you hit the index now. (Take off the FORCE INDEX on that part of the query).

Also, even given this issue, it's possible the slow down may be memory related. That is, you may have enough memory to process the table with a smaller number of rows, but it may be that when the table gets larger MySQL can't process the entire query in memory and is forced to start using disk to get the entire query handled. This would explain why there's a sudden drop off in performance.

If that's the case, then two things might help:

  1. Adding more memory (and tune the mysql config file to take advantage of it) so that the number of rows that can br processed at once is larger. This is at best a temporary solution.

  2. Tune the indexes (like I'm saying above) so that the number of rows that mysql needs to process is lower. If it can be more precise in picking the rows it selects for processing.

Upvotes: 1

Sebas
Sebas

Reputation: 21522

There's a moment when your query will be optimized around a model which might not be anymore valid for a given need.

A plan could be great but, even if the filters you are using in the where clause respect indexes definitions, it doesn't mean the parser doesn't parse may rows.

What you have to analize is how determinating are your indexes. For instance, if there's an index on "name, family name" in a "person" table, the performances are going to be poor if everybody has the same name and family name. The index is a real trap pulling down performances when it doesn't manage to be enough describing a certain segment of your datasets.

Upvotes: 1

triclosan
triclosan

Reputation: 5714

except a good plan you need to have enough resources to run query.

check buffers size and another critical parameters in your config.

And your query is?

Upvotes: 0

Related Questions