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->from(BeanFactory::newBean('TaskQueueItem'));

$query->offset(0)->limit(1);

$query->select(array('task_queue.id'));

$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  task_queue.id 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, task_queue.id 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?

 

Thanks

Outcomes