AnsweredAssumed Answered

How can I stop SugarQuery adding an extra order by?

Question asked by Jonathan Cutting on Mar 19, 2016

When this section of code is run (on a custom module but I don't think that's relevant), the query to the database has an extra order by added


$query = new SugarQuery();




$query->where()->equals('task_queue.type', $taskType);

$query->where()->equals('task_queue.status', 'ready');

$query->orderBy('task_queue.date_modified', 'ASC');

$results = $query->execute();


So the resulting query looks something like


SELECT id, task_queue.date_modified task_queue__date_modified

            FROM task_queue

            WHERE task_queue.deleted = 0

            AND task_queue.type = '{$taskType}'

            AND task_queue.status = 'ready'

            ORDER BY task_queue.date_modified ASC, DESC

            LIMIT 0,1


This prevents MySQL from using the index I've added to the date modified field, resulting in a query that takes over 6 seconds rather than a few thousandths of a second (because MySQL can't use an index where there is a combination of ASC and DESC order by clauses).


There's a section in the Sugar Query compiler that adds the extra order by for "stability", but in my example I'm less interested in stability than speed. I can't see a way to easily turn this off - is it possible?