Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Location
    Israel
    Posts
    5

    Unanswered: Index usage: (a = 1 AND b = 2) OR (b = 3 AND c = 4)

    I have a query similar to this:
    Code:
    SELECT *
    FROM tblName
    WHERE (fieldA = 1 AND fieldB = 2)
          OR (fieldA = 3 AND fieldB = 5)
          OR (fieldA = 6 AND fieldB = 1)
          OR (fieldA = 7 AND fieldB = 8);
    (Numbers are "random").

    If I have two indices on fieldA and fieldB, separately, will MySQL be able to use these when performing the query above? If not, will I have to create one index on both fieldA and fieldB, so MySQL can take advantage of it?

    Thanks in advance,
    Chen

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    As of version 4.0, MySQL can only use one index per table per query.

    As you suggest, you will have to use a concatenated index.

    However, if the match on A returns a small enough recordset, the subsequent match on B may be fine with a full scan.

  3. #3
    Join Date
    Jul 2002
    Location
    Israel
    Posts
    5
    Originally posted by Paul
    As of version 4.0, MySQL can only use one index per table per query.

    As you suggest, you will have to use a concatenated index.

    However, if the match on A returns a small enough recordset, the subsequent match on B may be fine with a full scan.
    Thanks Paul. This brings us to my next question, does it matter which condition is written first, or will MySQL know which one to perform first in order to get the smallest record set?

    Thanks!

  4. #4
    Join Date
    Jul 2002
    Location
    Israel
    Posts
    5
    Sorry, I've made a terrible mistake. The query should read:
    Code:
    SELECT *
    FROM tblName
    WHERE (fieldA < 1 AND fieldB = 2)
          OR (fieldA < 3 AND fieldB = 5)
          OR (fieldA < 6 AND fieldB = 1)
          OR (fieldA < 7 AND fieldB = 8);
    I've just tried this query on a real table and apparently no indices are being used, whether I use two or one. Is there any way to utilize at least one index, with this form?

  5. #5
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    does it matter which condition is written first, or will MySQL know which one to perform first in order to get the smallest record set?
    The optimizer should determine the right execution plan regardless of the actual order in the query.

    I've just tried this query on a real table and apparently no indices are being used, whether I use two or one. Is there any way to utilize at least one index, with this form?
    It's possible that he MySQL optimizer has decided that it's more efficient to do a full scan rather than use the index. Remember that an index requires two IO's for every row returned. If the result set is a significant portion of the table, it may never use an index.

    There are several hints you can use to tell the optimizer to add more weight to the index. For example:
    Code:
    SELECT *
    FROM tblName USE INDEX (index_name)
    WHERE (fieldA < 1 AND fieldB = 2)
          OR (fieldA < 3 AND fieldB = 5)
          OR (fieldA < 6 AND fieldB = 1)
          OR (fieldA < 7 AND fieldB = 8);
    This will force the optimizer to use the named index.

    another option is:
    Code:
    SELECT *
    FROM tblName FORCE INDEX 
    WHERE (fieldA < 1 AND fieldB = 2)
          OR (fieldA < 3 AND fieldB = 5)
          OR (fieldA < 6 AND fieldB = 1)
          OR (fieldA < 7 AND fieldB = 8);
    This will fool the optimzer into thinking a full table scan is very expensive, meaning it's more likely to use an index if available.

  6. #6
    Join Date
    Jul 2002
    Location
    Israel
    Posts
    5
    Ok, I'll run some tests and see what way is most efficient. I appreicate the help Paul.

Posting Permissions

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