Hi All, I have below sql, the sql is quite big.
select this_.listingprog_id as listingp1_490_3_, this_.marketplace_id as marketpl2_490_3_, this_.company_id as company3_490_3_, this_.listing_id as listing4_490_3_, this_.marketplace_listing_id as marketpl5_490_3_, this_.processing_flag as processing6_490_3_, this_.comments as comments490_3_, this_.successfully_submitted as successf8_490_3_, this_.date_added as date9_490_3_, this_.date_modified as date10_490_3_, this_.scheduled_end_time as scheduled11_490_3_, this_.lock_expiry_time as lock12_490_3_, this_.retries as retries490_3_, this_.resubmit_processing as resubmit14_490_3_, this_.bin_enabled as bin15_490_3_, this_.ebay_fixed_price_yn as ebay16_490_3_, listing1_.company_id as company1_485_0_, listing1_.listing_id as listing2_485_0_, listing1_.shipping_snapshot_id as shipping3_485_0_, listing1_.payment_snapshot_id as payment4_485_0_, listing1_.tax_snapshot_id as tax5_485_0_, listing1_.business_snapshot_id as business6_485_0_, listing1_.ebay_market_snapshot_id as ebay7_485_0_, listing1_.item_id as item8_485_0_, listing1_.ebay_ad_id as ebay9_485_0_, listing1_.marketplace_id as marketp10_485_0_, listing1_.marketplace_listing_id as marketp11_485_0_, listing1_.title as title485_0_, listing1_.sub_title as sub13_485_0_, listing1_.status as status485_0_, listing1_.date_added as date15_485_0_, listing1_.date_modified as date16_485_0_, listing1_.scheduled_start_time as scheduled17_485_0_, listing1_.scheduled_end_time as scheduled18_485_0_, listing1_.actual_start_time as actual19_485_0_, listing1_.actual_end_time as actual20_485_0_, listing1_.ad_schedule_queue_id as ad21_485_0_, listing1_.launch_profile_id as launch22_485_0_, listing1_.category_id as category23_485_0_, listing1_.category_id2 as category24_485_0_, listing1_.ebay_stores_category_id as ebay25_485_0_, listing1_.ebay_stores_category_id2 as ebay26_485_0_, listing1_.duration as duration485_0_, listing1_.quantity as quantity485_0_, listing1_.live_quantity as live29_485_0_, listing1_.revoke_quantity as revoke30_485_0_, listing1_.ebay_fixed_price_yn as ebay31_485_0_, listing1_.min_bid asmin32_485_0_, listing1_.bin_price as bin33_485_0_, listing1_.reserve_price as reserve34_485_0_, listing1_.current_price as current35_485_0_, listing1_.number_of_bids as number36_485_0_, listing1_.height as height485_0_, listing1_.weight as weight485_0_, listing1_.width as width485_0_, listing1_.depth as depth485_0_, listing1_.dimension_units as dimension41_485_0_, listing1_.weight_unit as weight42_485_0_, listing1_.package_size as package43_485_0_, listing1_.picture_url as picture44_485_0_, listing1_.gallery_url as gallery45_485_0_, listing1_.title_image_number as title46_485_0_, listing1_.gallery_image_type as gallery47_485_0_, listing1_.counter_type as counter48_485_0_, listing1_.counter_value as counter49_485_0_, listing1_.honesty_counter_mode as honesty50_485_0_, listing1_.honesty_counter_border as honesty51_485_0_, listing1_.deluxe_align as deluxe52_485_0_, listing1_.deluxe_theme as deluxe53_485_0_, listing1_.prev_marketplace_listing_id as prev54_485_0_, listing1_.relist_status as relist55_485_0_, listing1_.relist_offset as relist56_485_0_, listing1_.imported_from_mkt as imported57_485_0_, listing1_.email_winner as email58_485_0_, listing1_.checkout_redirect as checkout59_485_0_, listing1_.listing_type as listing60_485_0_, listing1_.run_postsale as run61_485_0_, listing1_.prefill_product_id as prefill62_485_0_, listing1_.stock_photo_yn as stock63_485_0_, listing1_.prefill_info_yn as prefill64_485_0_, listing1_.stock_photo_gallery_yn as stock65_485_0_, listing1_.prefill_attr_str as prefill66_485_0_, listing1_.stock_photo_url as stock67_485_0_, listing1_.prefill_details_url as prefill68_485_0_, listing1_.best_offer_enable_yn as best69_485_0_, listing1_.best_offer_option as best70_485_0_, listing1_.best_offer_decline_value as best71_485_0_, listing1_.fvf_fee as fvf72_485_0_, listing1_.listing_fees as listing73_485_0_, listing1_.purchase_cost as purchase74_485_0_, listing1_.iea_id as iea75_485_0_, listing1_.sco_listing as sco76_485_0_, listing1_.parts_acc as parts77_485_0_, listing1_.relaunched as relaunched485_0_, listing1_.cid as cid485_0_, delayedsub4_.company_id as company1_541_1_, delayedsub4_.listing_id as listing2_541_1_, delayedsub4_.submission_id as submission3_541_1_, delayedsub4_.marketplace_id as marketpl4_541_1_, delayedsub4_.attempt_number as attempt5_541_1_, delayedsub4_.submit_time as submit6_541_1_, delayedsub4_.window_start_time as window7_541_1_, delayedsub4_.window_end_time as window8_541_1_, delayedsub4_.processing_flag as processing9_541_1_, delayedsub4_.lock_expiry_time as lock10_541_1_, delayedsub4_.successfully_submitted as success11_541_1_, delayedsub4_.comments as comments541_1_, delayedsub4_.date_added as date13_541_1_, delayedsub4_.date_modified as date14_541_1_, delayedsub4_.sequence_id as sequence15_541_1_, delayedsub4_.archive_yn as archive16_541_1_, delayedsub4_.ad_schedule_queue_id as ad17_541_1_, listingdes5_.company_id as company1_482_2_, listingdes5_.listing_id as listing2_482_2_, listingdes5_.desc_prefix as desc3_482_2_, listingdes5_.description as descript4_482_2_, listingdes5_.desc_suffix as desc5_482_2_, listingdes5_.date_added as date6_482_2_, listingdes5_.date_modified as date7_482_2_ from list_prog this_ inner join list listing1_ on this_.company_id=listing1_.company_id and this_.listing_id=listing1_.listing_id left outer join del_sub delayedsub4_ on listing1_.company_id=delayedsub4_.company_id and listing1_.listing_id=delayedsub4_.listing_id left outer join ting_desc listingdes5_ on listing1_.company_id=listingdes5_.company_id and listing1_.listing_id=listingdes5_.listing_id where this_.successfully_submitted='N' and this_.scheduled_end_time>'2009-12-15 21:00:04' and listing1_.ebay_fixed_price_yn='Y' and listing1_.imported_from_mkt='Y' and listing1_.run_postsale='N' and listing1_.marketplace_id in (1, 6) and listing1_.status='A'; +----+-------------+--------------+--------+------------------------------------------------------------------------+---------------------+---------+-----------------------------------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+------------------------------------------------------------------------+---------------------+---------+-----------------------------------------------------+-------+-------------+ | 1 | SIMPLE | this_ | ref | listing_in_prog_idx5,listingprog_idx2,listingprog_idx3,listinprog_idx6 | listingprog_idx3 | 3 | const | 17136 | Using where | | 1 | SIMPLE | listing1_ | eq_ref | PRIMARY,LISTING_UK,LISTING_CID_STAT_MID_IDX,listing_new_dm_idx111 | PRIMARY | 12 | s12.this_.company_id,s12.this_.listing_id | 1 | Using where | | 1 | SIMPLE | delayedsub4_ | ref | delayed_submit_temp,delayed_submit_temp2 | delayed_submit_temp | 8 | s12.listing1_.LISTING_ID | 1 | | | 1 | SIMPLE | listingdes5_ | eq_ref | PRIMARY | PRIMARY | 12 | s12.listing1_.COMPANY_ID,s12.listing1_.LISTING_ID | 1 | | +----+-------------+--------------+--------+------------------------------------------------------------------------+---------------------+---------+-----------------------------------------------------+-------+-------------+ As it can be seen, its using the index of the where condition "KEY `listingprog_idx3` (`successfully_submitted`,`processing_flag`,`scheduled_end_time`)" and not the index of join condition. This is causing the query to taking time for execution. Anyways of tuning this regards anand
