ORDER BY with OR disjunctions is very slow, even when order field is indexed

SQL++
4

It might give little better. Other option is avoid fetch till you have final 500 documents and then use N1QL or SDK fetch them.

SELECT t1.multimedia, t1.titles.title, t1.publicationDate
FROM dataindexfts AS t1 USE KEYS (
SELECT RAW t.id
FROM ( (SELECT META().id, publicationDate  FROM dataindexfts WHERE sectionId="90" AND publicationDate <= '2021-09-15 16:45:04' AND published=TRUE AND type="news" AND META().id LIKE "editorial::%" ORDER BY publicationDate DESC LIMIT 500)
        UNION ALL
       (SELECT  META().id, publicationDate  FROM dataindexfts WHERE sectionId="93" AND publicationDate <= '2021-09-15 16:45:04' AND published=TRUE AND type="news" AND META().id LIKE "editorial::%" ORDER BY publicationDate DESC LIMIT 500)
       UNION ALL
       (SELECT META().id, publicationDate  FROM dataindexfts WHERE sectionId="94" AND publicationDate <= '2021-09-15 16:45:04' AND published=TRUE AND type="news" AND META().id LIKE "editorial::%" ORDER BY publicationDate DESC LIMIT 500)) AS t
ORDER BY t.publicationDate DESc
LIMIT 500);