List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:December 22 2009 9:49am
Subject:sql tuning
View as plain text  
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

Thread
sql tuningAnanda Kumar22 Dec