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

[don . tequila]

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" <ilya.kasnach...@gmail.com> Date: 9. April 2021 To: user@ignite.apache.org 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 <don.tequ...@gmx.de>: 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/