-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts and sample code, check out 
-- 	http://www.SQLSkills.com
-- For "More than Just Training," see the Industry Experts at 
-- 	http://www.SolidQualityLearning.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SET STATISTICS IO ON
-- Turn Graphical Showplan ON (Ctrl+K)

USE pubs
go

SELECT p.pub_name, t.title, a.au_lname
	FROM Publishers AS p
		INNER JOIN Titles AS t ON p.pub_id = t.pub_id
		INNER JOIN titleauthor AS ta ON ta.title_id = t.title_id
		INNER JOIN authors AS a ON a.au_id = ta.au_id
WHERE t.type= 'business'

-- Where is all the cost? (Highest number in showplan output...)
	-- Should be in titles table roughly 65%!

-- SO - what does the titles table need in this query?
-- Search List		title 
-- Join Cond1		pub_id
-- Join Cond2		title_id
-- Search Arg		type

-- Create "test" Indexes with priority (first column - or high order element) 
-- given to the search argument or the join and come up with some test cases... 
-- Try out the query again and see which one it chooses! Always put the cols of the
-- select list last!!!! They are the least significant!

-- First try just covering the sarg and join
CREATE INDEX testa_1 ON titles(type, pub_id)
CREATE INDEX testa_2 ON titles(type, title_id)
CREATE INDEX testa_3 ON titles(pub_id, type)
CREATE INDEX testa_4 ON titles(title_id, type)

-- Compare against first plan
SELECT p.pub_name, t.title, a.au_lname
	FROM Publishers AS p
		INNER JOIN Titles AS t ON p.pub_id = t.pub_id
		INNER JOIN titleauthor AS ta ON ta.title_id = t.title_id
		INNER JOIN authors AS a ON a.au_id = ta.au_id
WHERE t.type= 'business'

SELECT p.pub_name, t.title, a.au_lname
	FROM Publishers AS p
		INNER JOIN Titles AS t WITH (index (0))
			ON p.pub_id = t.pub_id
		INNER JOIN titleauthor AS ta ON ta.title_id = t.title_id
		INNER JOIN authors AS a ON a.au_id = ta.au_id
WHERE t.type= 'business'

-- What about covering the QUERY...
-- Prioritizing for sarg (then join cond1, then jc2, then select list)
	CREATE INDEX testb_1 ON titles(type, pub_id, title_id, title)
-- Prioritizing for sarg (then jc2, then jc1, then select list)
	CREATE INDEX testb_2 ON titles(type, title_id, pub_id, title)

-- Prioritizing for join(s) (jc1, sarg, jc2, select list)
	CREATE INDEX testb_3 ON titles(pub_id, type, title_id, title)
-- Prioritizing for join(s) (jc2, sarg, jc1, select list)
	CREATE INDEX testb_4 ON titles(title_id, type, pub_id, title)

-- Run the query again to see which one it chooses?

SELECT p.pub_name, t.title, a.au_lname
	FROM Publishers AS p
		INNER JOIN Titles AS t ON p.pub_id = t.pub_id
		INNER JOIN titleauthor AS ta ON ta.title_id = t.title_id
		INNER JOIN authors AS a ON a.au_id = ta.au_id
WHERE t.type= 'business'

SELECT p.pub_name, t.title, a.au_lname
	FROM Publishers AS p
		INNER JOIN Titles AS t WITH (index (testa_1))
			ON p.pub_id = t.pub_id
		INNER JOIN titleauthor AS ta ON ta.title_id = t.title_id
		INNER JOIN authors AS a ON a.au_id = ta.au_id
WHERE t.type= 'business'

SELECT p.pub_name, t.title, a.au_lname
	FROM Publishers AS p
		INNER JOIN Titles AS t WITH (index (0))
			ON p.pub_id = t.pub_id
		INNER JOIN titleauthor AS ta ON ta.title_id = t.title_id
		INNER JOIN authors AS a ON a.au_id = ta.au_id
WHERE t.type= 'business'

-- Seems to like testb_2, so you can drop the other indexes!

-- Need to continue and see if you can tweak it more... 
-- Run the query again to where the higest cost is now?

SELECT p.pub_name, t.title, a.au_lname
	FROM Publishers AS p
		INNER JOIN Titles AS t ON p.pub_id = t.pub_id
		INNER JOIN titleauthor AS ta ON ta.title_id = t.title_id
		INNER JOIN authors AS a ON a.au_id = ta.au_id
WHERE t.type= 'business'

-- to drop all of the indexes:
DROP INDEX titles.testa_1
DROP INDEX titles.testa_2
DROP INDEX titles.testa_3
DROP INDEX titles.testa_4
DROP INDEX titles.testb_1
--DROP INDEX titles.testb_2
DROP INDEX titles.testb_3
DROP INDEX titles.testb_4