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]>: > 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]> > Date: 9. April 2021 > To: [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]>: > >> 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/ >> >
