SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Index Help

  1. #1
    SitePoint Zealot csi95's Avatar
    Join Date
    Jan 2005
    Location
    Albany, NY
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Index Help

    Okay, I'm starting to question myself now....

    I'm trying to tune a complex SQL Server database. There are a number of queries that are running long, and I'm trying to come up with a good balance of indexes to make each run smoothly without messing up the other or quadrupling the overall size of the DB because I've got 200 indexes.

    As I understand it, you want to index on the fields in the WHERE clause. So if I had a query like:

    SELECT name, email
    FROM myTable
    WHERE state = 'NY'


    I would want an index on the "state" field.

    Of course, my queries are much, much more complex than that. For example:

    SELECT p.id, p.code, p.name, p.thumbnail, p.mfrpartnum, p.price, m.name AS mfr, '' AS pricing, '' AS type, ma.minimum
    FROM CSICproducts p INNER JOIN
    CUApricing pr ON p.id = pr.id LEFT OUTER JOIN
    CUAmaps ma ON p.mfrpartnum = ma.mfrpartnum LEFT OUTER JOIN
    CSICmanufacturers m ON p.store = m.store AND p.manufacturer = m.id
    WHERE (p.store = 8) AND (p.active = 'Y') AND (pr.type = '') AND ((p.code LIKE '%PJ550-2%') OR (m.[name] + p.mfrpartnum + p.[name] LIKE '%PJ5502%'))
    ORDER BY p.name ASC


    So, in this case, I'm a bit lost. I can obviously index on the fields in the WHERE clause, but I'm guessing I also index on the JOINS, right? If so, then should I have an index on id and type for the CUApricing table, for example?

    Then of course there's the field order. I assume that makes a big difference in the index, right? An index on id and type might work great on this query, but for another type and id might be better, right?

    I'm hoping that someone can help me confirm that I'm on the right track, and perhaps offer up some suggestions. I'm trying to use the Index Tuning Wizard, but I can't get trace information off of the production SQL Server, so it's not too effective.
    Join the EasyImage Affiliate Program!
    30% commission on all sales
    Conversion rates as high as 20%
    Dedicated Affiliate Manager to help you succeed!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes you are on the right track, and yes, you would usually want to create indexes on foreigm keys (primary keys, of course, have their own indexes created automatically)

    but of more concern is your query

    when you put a condition on a right table in a left outer join (as you do for m.[name]), then you are effectively turning the outer join into an inner join, so you might as well write it as an inner join, as this can sometimes allow the optimizer to come up with better access paths

    furthermore, any condition using LIKE where there's a leading wildcard is going to require a scan -- either an index scan or a table scan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot csi95's Avatar
    Join Date
    Jan 2005
    Location
    Albany, NY
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the quick response, Rudy.

    The LIKE with a leading wildcard is unique to this one query, and I understand it's tough to get any decent performance with that kind of query. What I wanted to show was that I'm working with some complex queries, including lots of joins.

    Thanks for the suggestion on the query changes. I can't really make the LEFT JOIN on the CSICmanufacturer's table into an INNER JOIN, because there isn't a one-to-one relationship between it and the CSICproducts table. Some products don't have a manufacturer listed, so they would end up being excluded from the query, which isn't what I want.

    One thing that would help is if I knew for sure how a query with a join translates into index usage. Using my query as an example, would an appropriate index for the CSICmanufacturers table be store, id, name? I'm guessing at that sequence because that is the order in which those fields appear in the query. Is this a correct assumption?

    Also, should I have an index for the ORDER BY clause? Should it be it's own index on that field, or part of a larger index that encompasses all the fields for that table?
    Join the EasyImage Affiliate Program!
    30% commission on all sales
    Conversion rates as high as 20%
    Dedicated Affiliate Manager to help you succeed!

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,

    maybe this article will help

    I think you should put an index on each of the foreign key fields in the child tables, for example on

    Code:
    CUAmaps.mfrpartnum

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by csi95
    Some products don't have a manufacturer listed, so they would end up being excluded from the query, which isn't what I want.
    i understand, and that's what a left outer join is for

    on closer examination i see that your "m" condition is inside an OR so i guess you're okay

    just remember, though, that if you write a condition on a right table column, you'll in effect get an inner join (unless you're testing for NULL)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot csi95's Avatar
    Join Date
    Jan 2005
    Location
    Albany, NY
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    furthermore, any condition using LIKE where there's a leading wildcard is going to require a scan -- either an index scan or a table scan
    I know that's true, but I'm at a loss on how to avoid it without taking a completely different approach to the search. Maybe a table of keywords, for example, rather than a search on the actual fields themselves.
    Join the EasyImage Affiliate Program!
    30% commission on all sales
    Conversion rates as high as 20%
    Dedicated Affiliate Manager to help you succeed!

  7. #7
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by csi95
    Then of course there's the field order. I assume that makes a big difference in the index, right? An index on id and type might work great on this query, but for another type and id might be better, right?
    No, not unless you are using the ORDER BY on these fields, which you aren't (at least not in the example).

    Quote Originally Posted by csi95
    I know that's true, but I'm at a loss on how to avoid it without taking a completely different approach to the search. Maybe a table of keywords, for example, rather than a search on the actual fields themselves.
    Are you sure that you have indexed all of the foreign key fields in each table? What does the index tuning wizard tell you?

    Maybe you will have to take a completely different approach to the search, it is the searches on "p.code LIKE '%PJ550-2%'" which are killing your performance. As Rudy says, the leading wildcard (%) forces a complete scan instead of the more efficient lookup.

    This might be an indication that your table is not normalized. What is in the p.code field before the "PJ550" part? Is it some kind of category code, and the contents of the field are actually "CAT1PJ550-2SOMETHING" ?? If that is the case (and you could show us some sample data) then you will need to split the data and store it separately.

    If you do need to search on parts of codes, then partitioning your data into a long then keywrds table won't help, if you are still searching like this: "select keyword from keywords where keyword like '%something%'".


    Perhaps it would be worth your time to take a look at the full text indexing capabiliies of MS SQL.

  8. #8
    SitePoint Zealot csi95's Avatar
    Join Date
    Jan 2005
    Location
    Albany, NY
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by asterix
    Perhaps it would be worth your time to take a look at the full text indexing capabiliies of MS SQL.
    I looked into this a while back. So close, and yet so far.

    The site this search is used for sells computer equipment. There are a lot of dashes, periods and quotes used in the part numbers and descriptions. For example:

    DRU-700A (part number)
    P02MS.A01 (part number)
    5' Cat 5e Cable (product name)

    All of those characters are considered "word breakers" for the full text indexing, meaning that you can't search for "A01" and come up with "P02MS.A01".

    From what I understand, that list of stop characters can be set at the server level, but not at the database level. Since I'm on a shared SQL Server, I can't get them changed, making Full Text Indexing useless for me (unless there's some other magic that I don't know about).
    Join the EasyImage Affiliate Program!
    30% commission on all sales
    Conversion rates as high as 20%
    Dedicated Affiliate Manager to help you succeed!

  9. #9
    SitePoint Zealot csi95's Avatar
    Join Date
    Jan 2005
    Location
    Albany, NY
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone? Any ideas?
    Join the EasyImage Affiliate Program!
    30% commission on all sales
    Conversion rates as high as 20%
    Dedicated Affiliate Manager to help you succeed!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •