Use Indexes, please.

And not only use indexes, create them for columns that have meaning. Also, the plural of Index is Indices but no one uses that word including Brent Ozar.

The Index Problem

Not Creating Indexes

I still see on a semi-regular occurrence CAR Reports that highlight some tables with no indexes. When creating a new table with a very limited dataset, its easy for this to get overlooked because looking up 1 or 3 records in a table isn't really going to be slow. It's near impossible for that operation to be slow so its possible that during dev and testing, there is no slowness because the dataset tested with is tiny. So we can, and should, use the CAR report to highlight when indexes are not created as well as a manual review of indexes and their usage as part of a pull request.

Creating Poor or Meaningless Indexes

Far less frequently I see that indexes are created on a table just to satisfy the "does it have an index" requirement but the index itself is of poor quality or is meaningless. For instance, a low quality index would be on something like "Name" when the table itself has other columns that are far better options like some kind of 10 character identifier, consider SalesTable.SalesId vs SalesTable.SalesName. Some tables only have a name and description so name is fine in that context when there are better options, always use the better options. If one is not present, find a way to make one present. Add a number sequence for a table if need be. Also every record has a RecId so you can use that for relations and generally that's much more performance oriented ( if you can also deal with some of the interesting side effects of an empty relationship ). Optimize for lookup by ID rather than location by search.

Other Stuff to Consider

When querying for data, you will always want to:

• Ensure fields used in the WHERE and JOIN conditions are backed by appropriate indexes
• Validate with a trace: if the query is scanning large tables, you’re paying for it
• Avoid “creative” filtering on unindexed columns in large transactional tables whenever possible
• In development, synthesize data if appropriate to get a more accurate representation of how the code will respond with a large data volume you'll see in production or at a later date 

You can also explicitly guide index usage in X++ in specific cases (sparingly), but the real win is designing the right indexes and writing sargable predicates. By way of example, this is how not to do it:
 

while select custTrans
    where year(custTrans.TransDate) == 2024  // function on indexed field = full scan = slow
{
    info(custTrans.Voucher);
}

And this is a much better way to do the same thing:

date fromDate = str2Date("01/01/2024", 213);
date toDate   = str2Date("12/31/2024", 213);

while select custTrans
    where custTrans.TransDate >= fromDate
       && custTrans.TransDate <= toDate     // range seek on index = fast
{
    info(custTrans.Voucher);
}

RecId present an interesting way to build relationships as the relation is now on the extended data type under "table reference" and not the table itself. Consider MyHeaderTable and MyLineTable. You can create an extended data type for MyHeaderTable.RecId, extending RecId, set property "reference table" on the EDT to MyHeaderTable, then add a table reference on RecId = MyHeaderTable.RecId. Now you can use that EDT on MyLineTable and the relation will be created whenever you click/drag the EDT onto the table as a field. This can make using RecId based indexes very easy to implement.