2

I am working on a search query in MySQL. I am getting this from explain:

Explain

Original screenshot

These are my indexes:

Indexes

Table definition:

Table

Original screenshot

Query:

SELECT
    `t`.`id` AS `t0_c0`, 
    `t`.`facebook_id` AS `t0_c1`,
    `t`.`first_name` AS `t0_c2`, 
    `t`.`last_name` AS `t0_c3`,
    `t`.`company_name` AS `t0_c4`, 
    `t`.`member_type` AS `t0_c6`,
    `t`.`gender` AS `t0_c7`, 
    `t`.`birth_date` AS `t0_c15`, 
    `t`.`zip` AS `t0_c22`, 
    `t`.`photo` AS `t0_c25`, 
    `t`.`password` AS `t0_c5`,
    `t`.`email` AS `t0_c8`, 
    `t`.`invite_code` AS `t0_c9`,
    `t`.`can_receive_connection_credits` AS `t0_c10`,
    `t`.`connection_credits` AS `t0_c11`, 
    `t`.`phone` AS `t0_c12`,
    `t`.`fax` AS `t0_c13`, 
    `t`.`website` AS `t0_c14`, 
    `t`.`locale` AS `t0_c16`, 
    `t`.`address` AS `t0_c17`, 
    `t`.`time_at_address` AS `t0_c18`, 
    `t`.`country` AS `t0_c19`, 
    `t`.`state` AS `t0_c20`,
    `t`.`city` AS `t0_c21`, 
    `t`.`zip_longitude` AS `t0_c23`,
    `t`.`zip_latitude` AS `t0_c24`, 
    `t`.`video` AS `t0_c26`, 
    `t`.`passwd` AS `t0_c27`, 
    `t`.`username` AS `t0_c28`, 
    `t`.`about_me` AS `t0_c29`,
    `t`.`how_far_to_travel` AS `t0_c30`, 
    `t`.`education` AS `t0_c31`,
    `t`.`certifications` AS `t0_c32`, 
    `t`.`work_legal` AS `t0_c33`,
    `t`.`has_transportation` AS `t0_c34`, 
    `t`.`ok_with_pets` AS `t0_c35`,
    `t`.`non_smoker` AS `t0_c36`, 
    `t`.`health_insurance` AS `t0_c37`,
    `t`.`languages` AS `t0_c38`, 
    `t`.`communications` AS `t0_c39`,
    `t`.`describe_ideal_job` AS `t0_c40`, 
    `t`.`what_children_like` AS `t0_c41`, 
    `t`.`comfort_children` AS `t0_c42`, 
    `t`.`favorite_books` AS `t0_c43`, 
    `t`.`favorite_music` AS `t0_c44`, 
    `t`.`favorite_movies` AS `t0_c45`, 
    `t`.`hobbies` AS `t0_c46`, 
    `t`.`profile_status` AS `t0_c47`,
    `t`.`profile_visits` AS `t0_c48`, 
    `t`.`background_checked` AS `t0_c49`, 
    `t`.`tozca_verified` AS `t0_c50`, 
    `t`.`verified_date` AS `t0_c51`, 
    `t`.`bchecked_date` AS `t0_c52`, 
    `t`.`verification_paid` AS `t0_c53`, 
    `t`.`background_paid` AS `t0_c54`,
    `t`.`verification_paid_date` AS `t0_c55`, 
    `t`.`background_paid_date` AS `t0_c56`, 
    `t`.`profile_cim_id` AS `t0_c57`, 
    `t`.`payment_cim_id` AS `t0_c58`, 
    `t`.`shipping_cim_id` AS `t0_c59`,
    `t`.`paypal_securetokenid` AS `t0_c60`, 
    `t`.`paypal_productinprocess` AS `t0_c61`, 
    `t`.`verified_transid` AS `t0_c62`,
    `t`.`verified_serialized` AS `t0_c63`, 
    `t`.`background_transid` AS `t0_c64`, 
    `t`.`background_serialized` AS `t0_c65`,
    `t`.`verification_results` AS `t0_c66`, 
    `t`.`background_offenders` AS `t0_c67`, 
    `t`.`date_added` AS `t0_c68`, 
    `t`.`date_modified` AS `t0_c69`, 
    `t`.`ip_added` AS `t0_c70`, 
    `t`.`ip_modified` AS `t0_c71`,
    `t`.`last_login` AS `t0_c72`, 
    `t`.`last_match` AS `t0_c73`,
    `t`.`published` AS `t0_c74`, 
    `t`.`membership_inprocess` AS `t0_c75`,
    `t`.`membership_ends` AS `t0_c76`, 
    `t`.`profile_completed` AS `t0_c77`, 
    `t`.`storage_used` AS `t0_c78`, 
    `t`.`is_claimed` AS `t0_c79`, 
    `t`.`token` AS `t0_c80`, 
    `t`.`slug` AS `t0_c81`, 
    t.id as myid, 
    CASE memberships.trial 
        WHEN 1 THEN 3 
        WHEN 0 THEN 
            CASE mems.is_basic 
                WHEN 1 THEN 2 
                WHEN 0 THEN 1 
            END 
    END AS priority,
    `roles`.`users_id` AS `t1_c0`, 
    `roles`.`roles_id` AS `t1_c1`,
    `services`.`id` AS `t2_c0`, 
    `services`.`users_id` AS `t2_c1`,
    `services`.`services_id` AS `t2_c2`, 
    `services`.`subjects` AS `t2_c3`,
    `services`.`years_experience` AS `t2_c4`, 
    `services`.`availability` AS `t2_c5`, 
    `services`.`online_tutoring` AS `t2_c6`,
    `services`.`hourly_rate` AS `t2_c7`, 
    `services`.`work_sick_children` AS `t2_c8`, 
    `services`.`twin_experience` AS `t2_c9`,
    `services`.`special_kid_experience` AS `t2_c10`,
    `services`.`special_kid_experience_description` AS `t2_c11`,
    `services`.`child_responsability_home` AS `t2_c12`,
    `services`.`working_live_in` AS `t2_c13`, 
    `services`.`specialize_in` AS `t2_c14`,
    `services`.`service_client_office` AS `t2_c15`,
    `services`.`service_client_home` AS `t2_c16`, 
    `services`.`agegroups` AS `t2_c17`,
    `services`.`additionalqualifications` AS `t2_c18`,
    `services`.`why_therapist` AS `t2_c19`, 
    `services`.`what_clients_like` AS `t2_c20`,
    `services`.`whois_your_rolemodel` AS `t2_c21`,
    `services`.`other_services` AS `t2_c22`,
    `services`.`ideal_employer_description` AS `t2_c23`,
    `services`.`what_children_like_aboutyou` AS `t2_c24`,
    `services`.`comfort_separation_anxiety` AS `t2_c25`,
    `services`.`favorite_childrens_book` AS `t2_c26`,
    `services`.`indoor_activities` AS `t2_c27`,
    `services`.`outdoor_activities` AS `t2_c28`,
    `services`.`movies_or_songs` AS `t2_c29`, 
    `services`.`date_added` AS `t2_c30`, 
    `services`.`ip_added` AS `t2_c31`,
    `services`.`date_modified` AS `t2_c32`, 
    `services`.`ip_modified` AS `t2_c33`, 
    `services`.`published` AS `t2_c34`,
    `services`.`licensend_therapist` AS `t2_c35`,
    `services`.`license_state` AS `t2_c36`, 
    `services`.`kind_of_therapist` AS `t2_c37`,
    `services`.`season` AS `t2_c38`, 
    `services`.`type` AS `t2_c39`, 
    `services`.`camper_gender` AS `t2_c40`,
    `services`.`activities_offered` AS `t2_c41`, 
    `services`.`phylosophy` AS `t2_c42`, 
    `services`.`religious_affiliations` AS `t2_c43`,
    `services`.`language_immersion` AS `t2_c44`,
    `services`.`type_of_sport` AS `t2_c45`, 
    `services`.`activity_type` AS `t2_c46`, 
    `services`.`child_gender` AS `t2_c47`,
    `services`.`child_age` AS `t2_c48`, 
    `services`.`name` AS `t2_c49`,
    `services`.`address` AS `t2_c50`, 
    `services`.`phone` AS `t2_c51`,
    `services`.`map` AS `t2_c52`, 
    `services`.`website_link` AS `t2_c53`,
    `services`.`direct_email_contact` AS `t2_c54`,
    `services`.`type_of_vender` AS `t2_c55`, 
    `services`.`robot_indexed` AS `t2_c56`,
    `services`.`company_certifications` AS `t2_c57`,
    `services`.`art_activity` AS `t2_c58`, 
    `services`.`other_arts` AS `t2_c59`, 
    CASE 
        WHEN AVG(rate) IS NOT NULL 
        THEN AVG(rate) 
        ELSE 0 
    END as rreviews, 
    CASE 
        WHEN user_reviewed_id IS NOT NULL
        THEN CAST(count(user_reviewed_id) as UNSIGNED) 
        ELSE 0 
    END as nreviews,
    `reviews`.`id` AS `t3_c0`, 
    `memberships`.`id` AS `t4_c0`,
    `memberships`.`users_id` AS `t4_c1`, 
    `memberships`.`membership_id` AS `t4_c2`,
    `memberships`.`membership_name` AS `t4_c3`,
    `memberships`.`trial` AS `t4_c4`, 
    `memberships`.`date_start` AS `t4_c5`, 
    `memberships`.`date_end` AS `t4_c6`,
    `memberships`.`megabytes` AS `t4_c7`, 
    `memberships`.`gallery` AS `t4_c8`, 
    `memberships`.`multiple_locations` AS `t4_c9`,
    `memberships`.`related_business` AS `t4_c10`, 
    `memberships`.`price` AS `t4_c11`, 
    `memberships`.`membership_transid` AS `t4_c12`,
    `memberships`.`membership_serialized` AS `t4_c13`,
    `memberships`.`date_added` AS `t4_c14`, 
    `memberships`.`ip_added` AS `t4_c15`, 
    `memberships`.`date_modified` AS `t4_c16`,
    `memberships`.`ip_modified` AS `t4_c17`, 
    `memberships`.`published` AS `t4_c18`, 
    `mems`.`id` AS `t5_c0`, 
    `mems`.`name` AS `t5_c1`,
    `mems`.`is_basic` AS `t5_c2`, 
    `mems`.`role_id` AS `t5_c3`,
    `mems`.`megabytes` AS `t5_c4`, 
    `mems`.`gallery` AS `t5_c5`,
    `mems`.`multiple_locations` AS `t5_c6`, 
    `mems`.`related_business` AS `t5_c7`, 
    `mems`.`max_file_size` AS `t5_c8`, 
    `mems`.`date_added` AS `t5_c9`, 
    `mems`.`ip_added` AS `t5_c10`, 
    `mems`.`date_modified` AS `t5_c11`, 
    `mems`.`ip_modified` AS `t5_c12`, 
    `mems`.`published` AS `t5_c13` 
FROM `tozca_users` `t` 
LEFT OUTER JOIN `tozca_users_roles` `roles` 
    ON (`roles`.`users_id`=`t`.`id`) 
LEFT OUTER JOIN `tozca_users_services` `services`
    ON (`services`.`users_id`=`t`.`id`)
LEFT OUTER JOIN `tozca_users_reviews` `reviews` 
    ON (`reviews`.`user_reviewed_id`=`t`.`id`) 
LEFT OUTER JOIN `tozca_users_memberships` `memberships` 
    ON (`memberships`.`users_id`=`t`.`id`) 
LEFT OUTER JOIN `tozca_memberships` `mems` 
    ON (`memberships`.`membership_id`=`mems`.`id`) 
    FORCE INDEX(user_by_zip)
WHERE 
(
    services_id=2 
    AND services.published =1 
    AND 
    (
        roles.roles_id = 2
        OR roles.roles_id = 5
    )
    AND 
    (
        date_end >= "2015-07-29 18:47:03" 
        OR date_end IS NULL
    ) 
    AND t.zip IN
    (
        '91980','91911','91917','91915','91910','91913','91914','91902','91950','919HH',
        '92139','92118','92113','92114','921HH','92102','91935','92101','91977','92106',
        '91978','91945','92107','92105','92104','92103','92115','91941','92116','92110',
        '92108','91942','92019','92109','92120','92020','92119','92123','92111','92021',
        '92124','91901','92117','92037','92071','92122','92040','91916','92121','92126',
        '92131','907XX','92130','92129','92014','92064','92091','92075','92128','92067',
        '92127','92007','92065','92024','920HH','92029','92009','92025','92078','92027',
        '92069','92008','92070','92083','92026','92056','92054','92084','92057','92082',
        '92003','92060','92061','92086','90704','92059','92028','92672','92624','92673',
        '92629','92590','92536','92592','92675','92591','92677','92651','92539','92562',
        '92694','92563','92656','926HH','92625','92661','92653','92662','92657','92691',
        '92595','92692','92663','92660','92679','92688','92630','926XX','92627','92612',
        '92596','92618','92584','92646','92648','92530','92626','92614','92610','92604',
        '92587','92606','92532','92586','92708','92544','92620','90742','92707','92649',
        '92704','92647','90743','90731','92602','92543','91719','90732','92545','92782',
        '92780','92585','92676','92655','927HH','92701','92703','92548','92549','92683',
        '90275','90740','908HH','90803','92705','92843','907HH','92844','92706','90802',
        '90814','92883','90822','90813','92845','90804','90744','92570','90274','92866',
        '92840','92841','92868','92583','92869','90717','90720','90815','92582','90710',
        '90806','90680','92802','90505','92867','92861','90810','928XX','90630','92804',
        '92567','92571','90808','90745','90807','90716','90501','90277','92865','90502',
        '92806','92805','92881','90503','90715','90620','92801','90713','90623','92807',
        '90712','90746','92808','90805','90254','92882','90701','90703','92832','90504',
        '90248','90278','92833','90621','925HH','92831','92879','92887','92870','92886',
        '90706','90260','90266','92551','92508','90220','92518','90247','90221','90261',
        '90249','928HH','90723','90638','902HH','92835','92880','90650','90222','92230',
        '92503','90250','92823','90242','92220','90245','92505','92555','92553','90061',
        '92821','90262','92860','92504','90059','90604','90670','90303','90631','90304',
        '90603','92282','90241','92506','90280','92223','90605','90002','90044','90301',
        '90293','92557','90240','90047','90045','90305','90003','90602','90201','90001',
        '90302','91709','90292','906HH','90606','90255','92507','92320','91748','90056',
        '90043','90270','90660','92501','90291','90230','90040','92509','90058','91745',
        '904HH','91752','90066','90037','90601','90062','91765','90011','90405','90008',
        '90640','90401','91710','91789','91792','90232','90023','90022','90007','90404',
        '90018','90021','91744','90016','92313','90403','92399','90064','92373','90263',
        '91761','90402','900HH','90015','917HH','90014','90013','90025','90063','91766',
        '91746','92337','91754','90006','90019','92318','90033','90272','91733','92354',
        '90265','90017','90035','90071','91755','90067','91762','90005','90212','90012',
        '90057','92324','90010','90024','92374','91791','92316','91768','90211','91770',
        '90020','91743','90049','91790','90036','91763','91732','91803','90048','91731',
        '90004','91764','90026','90031','91767','92408','90032','92359','91723','91706',
        '92339','91724','90210','92335','91776','90038','90029','91801','90069','91722',
        '910HH','90028','90046','90077','923HH','90290','91780','91786','91773','91730',
        '92401','92410','90065','92376','91711','90039','90027','91030','91775','90042',
        '91750','91740','92411','90068','91108','92336','91007','92346','91739','91702',
        '91701','91302','91006','91204','90041','91010','91105','91205','91784','91608',
        '91301','91741','91016','92405','91106','91361','91604','91101','913HH','91737',
        '92404','91107','92377','91602','91403','91203','91423','93041','91436','91206',
        '91364','91356','91024','91207','91607','91202','91103','91505','93033','91601',
        '91316','91104','91506','91201','92305','91320','91367','91401','91502','91411',
        '93035','91377','91606','91501','91208','91362','91001','91307','92407','91303',
        '91405','91335','91504','91406','92382','91605','91360','91306','91011','91020',
        '93030','91304','93012','91402','91352','92358','91759','93010','91214','91325',
        '91343','91324','92315','92341','92325','92386','92317','917XX','910XX','92321',
        '91331','92314','92352','91042','91311','91040','91345','93065','92333','93003',
        '930HH','91326','91344','93004','93063','91340','93066','93021','91342','93001',
        '92397','93060','91321','91381','931HH','93563','93015','92345','93022','93013',
        '93109','91355','93101','93067','93040','91351','93103','93108','93110','93105',
        '93111','93117','91354','92372','92308','92371','93023','91384','91350','92356',
        '93553','92392','93544','93510','930XX','93543','92307','913XX','92394','93552',
        '93550','931XX','93591','93551','93463','93460','92368','92301','93532','93535',
        '93534','93441','93536','92342','93225','93222','93243','92327','93560','92311',
        '92365','92398','92347','93254','93516','93252','93523','93501','93561','93505',
        '93203','93268','93313','93311','93224','93531','93241','92310','93276','93453',
        '93307','932XX','93309','93304','93251','93528','93206','933HH','93301','93518',
        '93305','93312','93306','93554','933XX'
    )
    AND t.published=1
) 
GROUP BY t.id 
ORDER BY priority DESC, rreviews DESC
LIMIT 10

Any idea how to improve it?

  • 1
    Improve the query in what respect? Is it too long? Is it too slow? If slow, for how long does it run now and how long do you want it to take? How many rows are there in the tables? – mustaccio Jul 30 '15 at 2:45
  • Hi guys, thanks for the suggestions. This query is taking long time about 10 seconds for returning 10 rows. All records in this table are about 300k, how can I improve it?. – Eduardo Jul 30 '15 at 4:39
  • If this query returns the correct result you can change most of the LEFT joins to INNER, at least the joins to roles, services, memberships. Mysql's optimizer used to be quite bad in recognizing this. – dnoeth Jul 30 '15 at 7:24
  • Thanks @dnoeth. That small change reduced search speed by 2 seconds :). – Eduardo Jul 30 '15 at 12:08
  • I have removed the ORDER BY and now it is blazing fast. But I need those calculated fields to order, any ideas how to handle it in better way? – Eduardo Jul 30 '15 at 12:09
0

If the GROUP BY necessary? Try removing it; that will eliminate a significant performance hit. (Or it will give you far more rows than you expect.)

In t, this should help:

INDEX(published, zip) -- in that order

Since both of the 'columns' in ORDER BY priority DESC, rreviews DESC are computed, there is no way to avoid finding all the possible rows, then sorting them, before getting to the LIMIT. Hence, as you observed, removing the ORDER BY made it run a lot faster. (It probably grabbed the first 10 rows in the table and did not need to touch the other 15K rows.)

Also, since you are filtering on columns in 4 tables, there is no way to have an INDEX that reaches all the way to the LIMIT.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.