Re: SQL query performance with JOIN and ORDER BY or WHERE

Tue, 13 Apr 2021 04:10:51 -0700 

Hi,

Please try to enforce use index for sorted select.
It make sense when filter produce a lot pf results.
So, full table will be scanned by ordered index, but result will be not materialized on map nodes. 
Using the example of the first message, it looks like this:

SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ USE INDEX(IDX_JQ_QUEUED)
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20

When IDX_JQ_QUEUED was created on JOBQUEUE (QUEUED).
I cannot guarantee he performance boost. Just to try.

On 12.04.2021 23:02, Thomas Kramer wrote:


Hi Ilya,
unfortunately this also didn't help improving query performance. Not sure what else I can try. Or maybe it is expected? In my opinion it shouldn't take that long as the query without the ORDER BY clause is super fast. Since there is a index on the order field I would expect this should be fast.
Btw, I noticed that for some other queries the first call has this long execution while on every following call with the same SQL statement it returns within half a second. But I guess this is caching related and not the issue I see here? 

Best Regards,
Thomas.


On 12.04.21 13:15, Ilya Kasnacheev wrote:

Hello!

I think you can try a (QUEUEID, STATUS) index.

Or maybe a (STATUS, QUEUEID), probably makes sense to try both.

Regards,
--
Ilya Kasnacheev
сб, 10 апр. 2021 г. в 00:22, <[email protected] <mailto:[email protected]>>: 

    The QUEUED field is a BIGINT that contains timestamp from
    System.currentTimeMillis(), so it should be pretty easy to sort,
    shouldn’t it? Looks like the field STATUS (used in where
    clause) and field QUEUED (used in order clause) are not working
    optimal when used together. Does this make sense? Do I need to
    create an index on both together?

    I will take a look at UNION and WHERE EXISTS, I‘m not familiar
    with these statements.

    Thanks!


    On 09.04.21 at 17:37, Ilya Kasnacheev wrote:

    From: "Ilya Kasnacheev" <[email protected]
    <mailto:[email protected]>>
    Date: 9. April 2021
    To: [email protected] <mailto:[email protected]>
    Cc:
    Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
    Hello!

    ORDER BY will have to sort the whole table.

    I think that using index on QUEUED will be optimal here. What is
    the selectivity of this field? If it s boolean, you might as well
    use UNION queries.

    Have you tried joining JOBS via WHERE EXISTS?

    Regards,
-- Ilya Kasnacheev 



    пт, 9 апр. 2021 г. в 01:03, DonTequila <[email protected]
    <mailto:[email protected]>>:

        Hi,

        I have a SQL performance issue. There are indexes on both
        fields that are
        used in the ORDER BY clause and the WHERE clause.

        The following statement takes about 133941 ms with several
        warnings from
        IgniteH2Indexing:

        SELECT JQ._KEY
        FROM "JobQueue".JOBQUEUE AS JQ
        INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
        WHERE JQ.STATUS = 2
        ORDER BY JQ.QUEUED ASC
        LIMIT 20

        But when I remove the ORDER BY part or the WHERE part from
        the statement it
        returns in <10ms.

        What may I do wrong?

        Thanks,
        Thomas.




        --
        Sent from: http://apache-ignite-users.70518.x6.nabble.com/
        <http://apache-ignite-users.70518.x6.nabble.com/>


--
Taras Ledkov
Mail-To: [email protected]

Reply via email to