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.

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.

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

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