May 6, 2011 at 2:33 pm
Hi Everyone,
Can anyone possibly help with a solution for re-writing this query? The query is very long because of the LEFT Join and nulls that is brings back. If I do a regular join the query finishes sub second, but with the left join it takes 50 seconds. Any thoughts?
Thanks!
SELECT CSO.OBJECT_ID, CSO.TABLE_ID, CS.CONTENT_SET_ID, CS.DISPLAY_NAME, CS.CONTENT_ORDER, T.CONTEXT_OBJECT_ID, T.CONTEXT_TABLE_ID
FROM CONTENT_SET_OWNER_TBL CSO
JOIN CONTENT_SET_TBL CS ON CS.CONTENT_SET_ID = CSO.CONTENT_SET_ID
JOIN SUPPLIER_TBL S ON S.SUPPLIER_ID = CSO.OBJECT_ID
LEFT JOIN TARGETING_CONTEXT_TBL T ON CS.CONTENT_SET_ID = T.OBJECT_ID AND T.OBJECT_TABLE_ID = 7 AND T.STATUS_ID = 1
WHERE CS.CONTENT_SET_TYPE_ID = 2
AND CS.PAID_STATUS_ID = 2
AND CSO.STATUS_ID = 1
AND CS.STATUS_ID = 1
AND S.STATUS_ID = 1
AND S.IS_LISTED = 1;
May 6, 2011 at 2:35 pm
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 6, 2011 at 2:36 pm
Easiest way for us to be able to help is to see the execution plans. If you need help pulling them, see the 'index/tuning' link in my signature.
My guess is when you join you're severely restricting one of the other tables from a very large amount to a small one, but that's a straight guess until we can see the two plans side by side.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 6, 2011 at 2:41 pm
To be honest I am using MySQL, not by choice for this project so I am not sure how much this may help. THis is the output for the execution plan from MySQL
"id""select_type""table""type""possible_keys""key""key_len""ref""rows""Extra"
"1""SIMPLE""CS""ref""PRIMARY,XPK_CONTENT_SET_TBL,XIF1_CONTENT_SET_TBL,XIF3_CONTENT_SET_TBL""XIF3_CONTENT_SET_TBL""5""const""1926""Using where"
"1""SIMPLE""CSO""ref""XIF1_CONTENT_SET_OWNER_TBL,XIF3_CONTENT_SET_OWNER_TBL""XIF1_CONTENT_SET_OWNER_TBL""4""DIR_SUPPLY_PROD_RC2.CS.CONTENT_SET_ID""1""Using where"
"1""SIMPLE""T""ALL"\N\N\N\N"7243"""
"1""SIMPLE""S""eq_ref""PRIMARY,XPK_SUPPLIER_TBL,X1_SUPPLIER_TBL""PRIMARY""4""DIR_SUPPLY_PROD_RC2.CSO.OBJECT_ID""1""Using where"
May 6, 2011 at 2:43 pm
FIELD TYPE COLLATION NULL KEY DEFAULT Extra PRIVILEGES COMMENT
-------------------- ------------ --------------- ------ ------ ------- -------------- ------------------------------- -------
CONTENT_SET_OWNER_ID INT(11) (NULL) NO PRI (NULL) AUTO_INCREMENT SELECT,INSERT,UPDATE,REFERENCES
TABLE_ID INT(11) (NULL) NO MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
OBJECT_ID INT(11) (NULL) NO MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
CONTENT_SET_ID INT(11) (NULL) NO MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
STATUS_ID INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
VERSION INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
/*Index Information*/
---------------------
TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT
--------------------- ---------- -------------------------- ------------ -------------------- --------- ----------- -------- ------ ------ ---------- -------
CONTENT_SET_OWNER_TBL 0 PRIMARY 1 CONTENT_SET_OWNER_ID A 14260 (NULL) (NULL) BTREE
CONTENT_SET_OWNER_TBL 0 XPK_CONTENT_SET_OWNER_TBL 1 CONTENT_SET_OWNER_ID A 14260 (NULL) (NULL) BTREE
CONTENT_SET_OWNER_TBL 1 XIF1_CONTENT_SET_OWNER_TBL 1 CONTENT_SET_ID A 14260 (NULL) (NULL) BTREE
CONTENT_SET_OWNER_TBL 1 XIF2_CONTENT_SET_OWNER_TBL 1 TABLE_ID A 10 (NULL) (NULL) BTREE
CONTENT_SET_OWNER_TBL 1 XIF3_CONTENT_SET_OWNER_TBL 1 OBJECT_ID A 14260 (NULL) (NULL) BTREE
CONTENT_SET_OWNER_TBL 1 XIF3_CONTENT_SET_OWNER_TBL 2 TABLE_ID A 14260 (NULL) (NULL) BTREE
May 6, 2011 at 2:43 pm
/*Table: CONTENT_SET_TBL*/
--------------------------
/*Column Information*/
----------------------
FIELD TYPE COLLATION NULL KEY DEFAULT Extra PRIVILEGES COMMENT
------------------- ------------- --------------- ------ ------ -------- -------------- ------------------------------- -------
CONTENT_SET_ID INT(11) (NULL) NO PRI (NULL) AUTO_INCREMENT SELECT,INSERT,UPDATE,REFERENCES
CONTENT_SET_TYPE_ID INT(11) (NULL) YES MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
DISPLAY_NAME VARCHAR(128) utf8_general_ci NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
CONTENT_ORDER INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
MIME_TYPE_ID INT(11) (NULL) NO MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
PAID_STATUS_ID INT(11) (NULL) YES MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
MEDIA_STATUS_ID INT(11) (NULL) NO MUL 1 SELECT,INSERT,UPDATE,REFERENCES
DOCUMENT_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
AUTHOR VARCHAR(256) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
DURATION TIME (NULL) NO 00:00:00 SELECT,INSERT,UPDATE,REFERENCES
MEDIA_SOURCE_ID INT(11) (NULL) NO MUL 1 SELECT,INSERT,UPDATE,REFERENCES
PRICE DECIMAL(10,2) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
CURRENCY_ID INT(11) (NULL) NO MUL 1 SELECT,INSERT,UPDATE,REFERENCES
IS_PAID TINYINT(4) (NULL) NO 0 SELECT,INSERT,UPDATE,REFERENCES
IS_RESEARCH TINYINT(4) (NULL) NO 0 SELECT,INSERT,UPDATE,REFERENCES
FIGURE_COUNT INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
CHART_COUNT INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
PAGE_COUNT INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
ECOMM_LINK VARCHAR(1024) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
STATUS_ID INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
VERSION INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
/*Index Information*/
---------------------
TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT
--------------- ---------- ---------------------- ------------ ------------------- --------- ----------- -------- ------ ------ ---------- -------
CONTENT_SET_TBL 0 PRIMARY 1 CONTENT_SET_ID A 10607 (NULL) (NULL) BTREE
CONTENT_SET_TBL 0 XPK_CONTENT_SET_TBL 1 CONTENT_SET_ID A 10607 (NULL) (NULL) BTREE
CONTENT_SET_TBL 1 XIF1_CONTENT_SET_TBL 1 CONTENT_SET_TYPE_ID A 36 (NULL) (NULL) YES BTREE
CONTENT_SET_TBL 1 XIF3_CONTENT_SET_TBL 1 PAID_STATUS_ID A 4 (NULL) (NULL) YES BTREE
CONTENT_SET_TBL 1 XIF4_CONTENT_SET_TBL 1 MIME_TYPE_ID A 44 (NULL) (NULL) BTREE
CONTENT_SET_TBL 1 CONTENT_SET_TBL_ibfk_4 1 MEDIA_STATUS_ID A 6 (NULL) (NULL) BTREE
CONTENT_SET_TBL 1 CONTENT_SET_TBL_ibfk_5 1 CURRENCY_ID A 2 (NULL) (NULL) BTREE
CONTENT_SET_TBL 1 CONTENT_SET_TBL_ibfk_6 1 MEDIA_SOURCE_ID A 10 (NULL) (NULL) BTREE
May 6, 2011 at 2:44 pm
*Table: SUPPLIER_TBL*/
-----------------------
/*Column Information*/
----------------------
FIELD TYPE COLLATION NULL KEY DEFAULT Extra PRIVILEGES COMMENT
----------------- ------------- --------------- ------ ------ ------- -------------- ------------------------------- -------
SUPPLIER_ID INT(11) (NULL) NO PRI (NULL) AUTO_INCREMENT SELECT,INSERT,UPDATE,REFERENCES
NAME VARCHAR(128) utf8_general_ci NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
IS_LISTED TINYINT(4) (NULL) NO MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
SHORT_DESCRIPTION VARCHAR(256) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
URL VARCHAR(1024) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INDUSTRY_ID INT(11) (NULL) NO MUL 1 SELECT,INSERT,UPDATE,REFERENCES
INDUSTRY_OTHER VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
ANNUAL_REVENUE_ID INT(11) (NULL) YES MUL 1 SELECT,INSERT,UPDATE,REFERENCES
NUM_EMPLOYEES_ID INT(11) (NULL) NO MUL 1 SELECT,INSERT,UPDATE,REFERENCES
BRAND_NAME VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
LAST_LOGIN DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
ADVERTISER_SINCE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
SOURCE_TYPE_ID INT(11) (NULL) NO MUL 4 SELECT,INSERT,UPDATE,REFERENCES
SRC_IDENTIFIER VARCHAR(64) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
CREATOR_ID INT(11) (NULL) YES MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
REVIEW_STATUS_ID INT(11) (NULL) NO MUL 1 SELECT,INSERT,UPDATE,REFERENCES
IS_SVCS_PRT TINYINT(4) (NULL) NO 0 SELECT,INSERT,UPDATE,REFERENCES
IS_PUB_PRT TINYINT(4) (NULL) NO 0 SELECT,INSERT,UPDATE,REFERENCES
IS_CONT_PRT TINYINT(4) (NULL) NO 0 SELECT,INSERT,UPDATE,REFERENCES
ECOMM_SITE VARCHAR(1024) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
STATUS_ID INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
VERSION INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_DATE DATETIME (NULL) YES MUL (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
/*Index Information*/
---------------------
TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT
------------ ---------- ----------------------- ------------ ----------------- --------- ----------- -------- ------ ------ ---------- -------
SUPPLIER_TBL 0 PRIMARY 1 SUPPLIER_ID A 1527 (NULL) (NULL) BTREE
SUPPLIER_TBL 0 XPK_SUPPLIER_TBL 1 SUPPLIER_ID A 1527 (NULL) (NULL) BTREE
SUPPLIER_TBL 1 XIF4_SUPPLIER_TBL 1 ANNUAL_REVENUE_ID A 27 (NULL) (NULL) YES BTREE
SUPPLIER_TBL 1 XIF6_SUPPLIER_TBL 1 NUM_EMPLOYEES_ID A 31 (NULL) (NULL) BTREE
SUPPLIER_TBL 1 XIF7_SUPPLIER_TBL 1 SOURCE_TYPE_ID A 2 (NULL) (NULL) BTREE
SUPPLIER_TBL 1 XIF8_SUPPLIER_TBL 1 INDUSTRY_ID A 43 (NULL) (NULL) BTREE
SUPPLIER_TBL 1 SUPPLIER_TBL_ibfk_5 1 CREATOR_ID A 254 (NULL) (NULL) YES BTREE
SUPPLIER_TBL 1 SUPPLIER_TBL_ibfk_6 1 REVIEW_STATUS_ID A 18 (NULL) (NULL) BTREE
SUPPLIER_TBL 1 X1_SUPPLIER_TBL 1 IS_LISTED A 2 (NULL) (NULL) BTREE
SUPPLIER_TBL 1 X1_SUPPLIER_TBL 2 STATUS_ID A 10 (NULL) (NULL) YES BTREE
SUPPLIER_TBL 1 X1_CATEGORY_PRODUCT_TBL 1 UPDATED_DATE A 1527 (NULL) (NULL) YES BTREE
May 6, 2011 at 2:44 pm
/*Table: TARGETING_CONTEXT_TBL*/
--------------------------------
/*Column Information*/
----------------------
FIELD TYPE COLLATION NULL KEY DEFAULT Extra PRIVILEGES COMMENT
-------------------- ------------ --------------- ------ ------ ------- -------------- ------------------------------- -------
TARGETING_CONTEXT_ID INT(11) (NULL) NO PRI (NULL) AUTO_INCREMENT SELECT,INSERT,UPDATE,REFERENCES
OBJECT_TABLE_ID INT(11) (NULL) NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
OBJECT_ID INT(11) (NULL) NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
CONTEXT_TABLE_ID INT(11) (NULL) NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
CONTEXT_OBJECT_ID INT(11) (NULL) NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
STATUS_ID INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
VERSION INT(11) (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
INSERTED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_DATE DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
UPDATED_BY VARCHAR(128) utf8_general_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
/*Index Information*/
---------------------
TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT
--------------------- ---------- ------------------------- ------------ -------------------- --------- ----------- -------- ------ ------ ---------- -------
TARGETING_CONTEXT_TBL 0 PRIMARY 1 TARGETING_CONTEXT_ID A 6610 (NULL) (NULL) BTREE
TARGETING_CONTEXT_TBL 0 XPK_TARGETING_CONTEXT_TBL 1 TARGETING_CONTEXT_ID A 6610 (NULL) (NULL) BTREE
May 6, 2011 at 2:51 pm
adamwhelan4 (5/6/2011)
To be honest I am using MySQL, not by choice for this project so I am not sure how much this may help.
This is a SQL Server forum, most of us here are SQL people and aren't that familiar with MySQL. You may well get better and faster answers at a MySQL site, like http://forums.mySQL.com/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply