Over the past year I've been in a handful of platform engineering discussions where the question of "Does query order matter?" is asked. I've listened to claims and opinions by my peers, but no one had any evidence or documentation to support those claims.

>> Fact! Yes, the order of the query matters. <<

On a very large, un-optimized un-indexed set of records the proper order can save up to 90%. Without clearing the cache, subsequent queries were near identical, so the data below applies to only the first query after clearing the cache.

Here are the steps I went through...

Hypothesis

• Yes, the order of the query matters
• The method of scripted GlideRecord would not matter (AddEncodedQuery vs. AddQuery)

Setup

In Global Scope, on Kingston Personal Dev Instance

• Added field RandomStuff [u_random] to `ticket` table (string, 256 char, not indexed)
• Created 10M records each with random 256 characters placed in RandomStuff
• Manually changed 10 records to Closed (active=false)
• Copied a portion ("d8qwoxtwpa") from the random string on one of the active=false records.
• Ran the query both ways, changing order of the (Active = False) and (RandomStuff Contains "d8qwoxtwpa")
• Cleared cache between each test

Results

The version with (Active = False) placed first ran much faster in both tests. The differences between AddEncodedQuery and AddQuery was not noticeable.

Average over 10 tests

Time (in ms)	Test 1	Test 2
Active first	1.1	1.0
Contains first	11.9	12.2
Savings	90.8%	91.8%

Reference Scripts

Create 10M records

var numLoops = 10000000;

var passLen = 256;

for (var i=0; i<numLoops; i++) {

    var ticket = new GlideRecord('ticket');

    ticket.newRecord();

    ticket.setValue('short_description','Auto created number '+i);

    ticket.setValue('u_random',makePass(passLen));

    ticket.insert();

}

function makePass(length){

      var string_length = length;

      var randomstring = '';

      for (var i=0; i<string_length; i++) {

              randomstring += Math.random().toString(36).slice(-1);

      }

      return randomstring;

}

Test 1: Encoded Query Method (Fix script with 'Flush cache' checked)

var tableName = 'ticket';

var findThis = 'd8qwoxtwpa'; //From Ticket 0003

var query1 = 'u_randomLIKE'+findThis+'active=false';

timedSearch(tableName,query1);

var query2 = 'active=falseu_randomLIKE'+findThis;

timedSearch(tableName,query2);

function timedSearch(tableName,tableQuery){

    var stopwatch = new GlideStopWatch();

    var ticket = new GlideRecord(tableName);

    ticket.addEncodedQuery(tableQuery);

    ticket.query();

    var recordsFound = ticket.getRowCount();

    gs.print('Timer:'+stopwatch);

}

Test 2: AddQuery Method (Fix script with 'Flush cache' checked)

var tableName = 'ticket';

var findThis = 'd8qwoxtwpa'; //From Ticket 0003

//var query1 = 'u_randomLIKE'+findThis+'active=false';

var stopwatch = new GlideStopWatch();

var ticket = new GlideRecord(tableName);

ticket.addQuery('u_random', "CONTAINS", findThis);

ticket.addQuery('active',false);

ticket.query();

var recordsFound = ticket.getRowCount();

gs.print('Query1 Timer:'+stopwatch);

//var query2 = 'active=falseu_randomLIKE'+findThis;

var stopwatch2 = new GlideStopWatch();

var ticket2 = new GlideRecord(tableName);

ticket2.addQuery('active',false);

ticket2.addQuery('u_random', "CONTAINS", findThis);

ticket2.query();

var recordsFound2 = ticket2.getRowCount();

gs.print('Query2 Timer:'+stopwatch2);