Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Question Unanswered: SQL optimizing and indexing problem

    I'm having difficulties to optimize my relatively simple query. The query fetches data from one table that have about 0,5 million rows. I'm using EXISTS clause to fetch the wanted rows and have an index created for that, but the query is still quite slow.

    Is there a alternative, faster, way to perform the following SQL?

    SELECT
    m1.document_id, m1.document, m1.attribute
    FROM
    myTable m1
    WHERE
    EXISTS ( SELECT * FROM myTable m2 WHERE attribute = 'attrName' AND m1.document_id = m2.document_id )
    ORDER BY
    m1.document_id, m1.attribute

    It seems that the EXISTS clause slows down the query dramatically.

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: SQL optimizing and indexing problem

    Then try this. If you have proper indexes, it will try to make use of it.

    SELECT m1.document_id, m1.document, m1.attribute
    FROM myTable m1, myTable m2
    WHERE m2.attribute = 'attrName'
    AND m1.document_id = m2.document_id
    ORDER BY m1.document_id, m1.attribute;

    And if you can sacrifice on the order by, do it.

    Hope this helps.

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Smile Re: SQL optimizing and indexing problem

    Thanks! The new SQL works fine! It dropped the execution time to 30% and now the clause is even more simpler.

    Originally posted by dbmadcap
    Then try this. If you have proper indexes, it will try to make use of it.

    SELECT m1.document_id, m1.document, m1.attribute
    FROM myTable m1, myTable m2
    WHERE m2.attribute = 'attrName'
    AND m1.document_id = m2.document_id
    ORDER BY m1.document_id, m1.attribute;

    And if you can sacrifice on the order by, do it.

    Hope this helps.

  4. #4
    Join Date
    Oct 2003
    Location
    Sofia
    Posts
    16
    That one script keep original business logic.

    SELECT
    m1.document_id,
    m1.document,
    m1.attribute
    FROM
    myTable m1
    WHERE
    m1.document_id in (SELECT
    document_id
    FROM
    myTable
    WHERE
    attribute = 'attrName')
    ORDER BY
    m1.document_id,
    m1.attribute

Posting Permissions

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