#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535

    When to use indexes


    I have the following table:
    Code:
    CREATE  TABLE IF NOT EXISTS users (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      firstname VARCHAR(45) NULL ,
      lastname VARCHAR(45) NULL ,
      username VARCHAR(45) NOT NULL ,
      otherData VARCHAR(45) NULL ,
      PRIMARY KEY (id) )
    ENGINE = InnoDB
    I have a query where the user provides a string, and matching users are returned:
    Code:
    SELECT id, CONCAT(firstname," ",lastname," (",username,")") AS value
    FROM users
    WHERE (firstname LIKE ? OR lastname LIKE ? OR username LIKE ?)
    Should I put an index on firstname, lastname, and username?

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    should you put an index on firstname, lastname, and username?

    if you mean a 3-column composite index, no, that might not be good enough

    should you put an index on firstname, lastname, or username?

    perhaps on each, yes, especially if you split your query up to remove the ORs from the WHERE clause, by having three separate queries UNIONed together

    it shouldn't be all that hard to experiment with all sorts of index scenarios, running an EXPLAIN after each to see the effect
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    Not a 3-column composite index, but individual indexes on all three. My understanding is that whether I have the WHERE clause filter all three with OR, or individually filter each and then UNION them, they would still benefit from the index. Does this make sense to others, or just me?

    As for as experimenting and running EXPLAIN, great suggestion. That being said, I presume that whether indexes provide value is dependent upon the amount of records I have in the associated tables, and the number of records will change over time. Additionally, if a table is never filtered or joined on a given column, it doesn't make much sense to index it. Are there rules of thumb when deciding to use indexes?

    Additionally, what if I have "SELECT id FROM users WHERE firstname LIKE ? AND another_column=123", and "another_column" will always limited the records to under 20. Yes, I am filtering on firstname thus perhaps a index is of value, but given the effective low number of resultant rows to filter over, maybe not worth it?
  6. #4
  7. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,951
    Rep Power
    4033
    Originally Posted by NotionCommotion
    Not a 3-column composite index, but individual indexes on all three. My understanding is that whether I have the WHERE clause filter all three with OR, or individually filter each and then UNION them, they would still benefit from the index. Does this make sense to others, or just me?
    A single query with OR conditions may or may not use an index. When executing the query MySQL will only use one index. With the three OR conditions like you have it currently, one index likely would not be beneficial so you'd still get a table scan.

    By using separate queries that are UNION'ed together you give mysql the ability to use different indexes for each query. In that case, having a separate index on each field would likely help.

    Originally Posted by NotionCommotion
    That being said, I presume that whether indexes provide value is dependent upon the amount of records I have in the associated tables, and the number of records will change over time. Additionally, if a table is never filtered or joined on a given column, it doesn't make much sense to index it.
    An index is only useful for fields which are involved in a JOIN, WHERE. If your field will never be used in any of those situations there is no reason to index it.


    Originally Posted by NotionCommotion
    Additionally, what if I have "SELECT id FROM users WHERE firstname LIKE ? AND another_column=123", and "another_column" will always limited the records to under 20. Yes, I am filtering on firstname thus perhaps a index is of value, but given the effective low number of resultant rows to filter over, maybe not worth it?
    If you have an index on another_column then mysql would likely use that, and then filter those results by firstname individually. As mentioned above, mysql will only use a single index. Mysql keeps statistics on each index about how unique it is so it knows how effective one index would be vs another at limiting a result set. It will use those statistics to try and select the best index to use when executing a query. It'll use whichever index it feels will be the most limiting, grab those results and apply any additional WHERE conditions individually until it gets the final result set.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by NotionCommotion
    As for as experimenting and running EXPLAIN, great suggestion. That being said, I presume that whether indexes provide value is dependent upon the amount of records I have in the associated tables, and the number of records will change over time.
    there are only two types of table volumes -- test size and live size


    Originally Posted by NotionCommotion
    Additionally, if a table is never filtered or joined on a given column, it doesn't make much sense to index it.
    now you've got it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo