Improve SQL Server 2005 Query Performance -

I have a course search engine and when I try to do a search, It seems you can try a search here

On that page you can see database tables and indexes, if any.

I'm not using stored procedures - queries are inline using ColdFusion.

I think I need to make some indexes, but I'm not sure how to (cluster, non-cluster) and on which column.

Thank you

You rule your WHERE There are some exceptions to this:

• If there are only one or two unique values ​​in the column (the canonical example of this is "penis" - possible values ​​with only "male" and "female", here index Does not make any sense) Generally, you want an index that is capable of restricting rows that need to be processed by a significant number (for example, an index which reduces the search space to only 50% It is not worth it, but which reduces it to 99% ).
• If you're searching for '% something' like the x, the index does not have any meaning if you assign a particular command to the rows If you think of an index to specify for, sort by x , if you are searching for "% some" is useless: you are still going to all the rows You have to scan.

So take a look at this matter where you are searching for "keyword" accounting. "According to your results page, it generates J.SQL:

  from SELECT * (maximum 10 ROW_NUMBER ()) (line by sq.name) AS line, from class * (SELECT C. *, P.providername, p.school, p.website, p. Type cpd_COURSES c, cpd_PROVIDERS p WHERE c.providerid = p.providerid and c.activatedYN = 'Y' and '% accounting%' such as 'accounting%' or '% accounting%' or 'c' Like CTTil)) class) AS TEMP where the row> = 1 and row    
 In this case, I would agree that  cpd_COURSES.providerid  is a foreign key for a  cpd_PROVIDERS.providerid , in which case you will get an index , Because it is already 
 
 Additionally, the  active YN  column is a T / F column and (according to my rule the possible value is only up to 50% A T / F column should not be indexed, either. 
 
 Finally, because of  x LIKE '% accounting%'  query To search with, you do not need any index on the name, title, or keyword - because it will never be used. 
 
 So in this case, the main thing that you need, make sure that  cpd_COURSES.providerid  is actually    cpd_PROVIDERS.providerid A foreign key for . 
 
 SQL Server Specific 
 
 Because you are using SQL Server, there are many tools in Management Studios, which can help you decide where to place the index . If you use the "index tuning wizard" then it's really great to let you know what will improve you in good performance. You can just cut your query in it, and use the recommendations to add indexes. Will come back together 
 
 You need to be careful with indexing while adding, because you have more indexes, slow code will be  INSERT  s and  UPDATE  s. So sometimes you'll need to consolidate the index, or if they do not get enough performance benefits, then ignore them completely. Some decisions are necessary.