Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: OR condition and indexes

    Here's the scenario I'm faced with:

    I have a table with columns A, B, C, D, and E. Columns A, B, and C are indexed. Now, when I run the following query:

    SELECT * FROM table myTable WHERE (A = 'value1' or A = 'value2') and B <= 'value3' and C = 'value4';

    .. the performance is really slow cause it is not utilzing the A/B/C index. However, if I split the OR condition into two different queries:

    SELECT * FROM table myTable WHERE (A = 'value1') and B <= 'value3' and C = 'value4';
    SELECT * FROM table myTable WHERE (A = 'value2') and B <= 'value3' and C = 'value4';

    .. the index is used and they both return almost instantaneously.

    In my scenario, the latter solution is actually fine for me to use. But curiosity is killing me: Is there a way for me to do this in one query without UNION'ing the two separate queries?

    I tried the following queries, but they still don't utilize the index:

    SELECT * FROM table myTable WHERE A IN ('value1', 'value2') and B <= 'value3' and C = 'value4';

    SELECT * FROM table myTable WHERE (A = 'value1' and B <= 'value3' and C = 'value4') or (A = 'value2' and B <= 'value3' and C = 'value4') ;

    Thanks in advance.

  2. #2
    Join Date
    Sep 2009
    Posts
    39
    Code:
    alter table myTable add index(A,B,C)
    remove index which contains single field.

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    "Columns A, B, C ad indexed" does not mean much. You could have:
    • index_1 (A)
    • index_2 (B)
    • index_3 (A, B, C)
    • index _4 (C, A)
    ... as well as combinations. The optimiser chooses which index it can use based on the actual values you supply (between the quotes) vs the statistics stored for the columns. If the total of (A = "value1" plus A = "value2") represents a large portion of the table (based on stats), it may choose a table scan (incorrectly). Also the version makes a lot of difference. ASE 15.0 has a completely re-written optimiser, and it has not settled down yet.

    If the second SQL returns immediately, that suggests a covered query; does you select lis contain A, B, C only.

    Have you checked:
    1 Update index statistics recently
    2 There are no datatype mismatches (your code vs the table columns)

    If you post again, please post DDL for the table and index, plus your SQL (you can obfuscate the table/columns if you need to).
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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