ReidLarson

Performance Impact When Querying Text ID Fields With "=="

Discussion created by ReidLarson on Nov 12, 2018
Latest reply on Nov 12, 2018 by steve_ssh

Background

I have a table with ~225,000 records. One field is a text foreign ID field related to a table with a text primary ID field set up as a UUID. The foreign ID field is set to index all with a language of Unicode. When I search this field for the ID of one of the records in the related table I get ~55,000 records returned.

 

Performance

Searching this field with

"==" & $id

takes ~6000-7000 milliseconds.

 

Searching this field with

$id

takes ~10-20 milliseconds.

 

Potential Issues?

The standard advice is to use "==" to query text ID fields because, unlike number fields, searching for "174" will also return results for "1743". Because UUIDs are a fixed length, I don't think that would be a problem for this particular type of ID field. Are there any pitfalls I'm overlooking?

Outcomes