Geek asks and answers

How can I get a good EXPLAIN and a slow query?

How is it possible to have a good plan in EXPLAIN, as shown below, and have a slow query. With multiple lines using an index, no filesort.

The request is completed in 9 seconds. The main table has about 500 thousand rows.

When I had 250k rows in this table, the query ran in <1s.

Suggestions plz?

explain

Query (1. fields can be included according to the choice of the user 2. Without FORCE INDEX I got 14 s. 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

Request Profile

query profile

The result set always displays 15 rows. But the table property and property_attribute have about 500 thousand lines.

Thanks everyone

Armando Miani

+3
source share
4 answers

, , .

, , where, , , .

, . , ", " "", , . , , .

+1

EXPLAIN. MySQL 4.x, MySQL 5.x.

, MySQL , , MySQL ix_order_by_perf , 15 , LIMIT 15.

WHERE 500K , WHERE. , , "_keys".

, FORCE INDEX ORDER BY. , MySQL - , , ( ).

property_property_type_id, property_property_type_sale_id, property_property_housing_id , WHERE, .

+2

, :

(, ):

    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'

, property, , . , , , ( , , ).

-, ( ):

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

EXPLAIN, , . ( FORCE INDEX ).

, , , . , , , MySQL , . , .

, :

  • ( mysql, ), , , . .

  • Configure indexes (e.g., I say above) so that the number of rows that mysql needs to process is lower. If it can be more accurate when selecting the rows that it selects for processing.

+1
source

besides a good plan, you need to have enough resources to run the request.

check the size of the buffers and other critical parameters in your configuration.

And your request?

0
source

More articles:


All Articles