Does "consolidation" of query statements improve query performance? Assume we have a set of postal addresses A1, A2, A3, ... of the form:

A1 = 124, "Smith", "Rd"
A2 = 142, "Jones", "St"
A3 = 8190, "Smith", "Ln"
A4 = 142, "Via Gracia", NULL
A5 = 190, "Jones", "St"
A6 = 31425, "Wedgewood", "Cir"
A7 = 810, "Via Gracia", NULL
...


where A_N is a vector of 3 components: StreetNumberN, StreetNameN, StreetSuffixN.

No two vectors are ever equal component-for-component, however many share StreetName and StreetSuffix.

Translating this dataset directly into a SQL conditional, using a loop, one might obtain:

WHERE
    (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
 OR
    (StreetNumber = 142 AND StreetName = "Jones" AND StreetSuffix = "St")
 OR
    (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
 OR
    (StreetNumber = 142 AND StreetName = "Via Gracia")
 OR
    (StreetNumber = 190 AND StreetName = "Jones" AND StreetSuffix = "St")
 OR
    (StreetNumber = 31425 AND StreetName = "Wedgewood" AND StreetSuffix = "Cir")
 OR
    (StreetNumber = 810 AND StreetName = "Via Gracia")


Now, when the StreetName and StreetSuffix match for two condition statements, it is logically possible to  "consolidate" StreetNumbers:

WHERE
    (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
 OR
    (StreetNumber IN(142, 190) AND (StreetName = "Jones" AND StreetSuffix = "St"))
 OR
    (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
 OR
    (StreetNumber IN (142,810) AND StreetName = "Via Gracia")
 OR
    (StreetNumber = 31425 AND StreetName = "Wedgewood" AND StreetSuffix = "Cir")


Please keep in mind this is just a toy example to demonstrate the principle of "consolidation" that I mean. In practice, there would be many such "consolidations" to perform.

Is there/should there be any performance benefit in consolidating StreetNumbers as demonstrated above?
Reasonable question.  Let me set you straight on the relative costs of various components of the query.

OR is the de-optimizer.  Either of those formulations will work this way:


Scan the entire table
For each row, check the WHERE clause
Most or all of the OR parts will be evaluated to eliminate a given row, thereby making your suggest a small improvement.


Scanning the table and fetching every row is the most costly part.  Evaluating the WHERE is a minor part.

That is, any savings you get will be insignificant in the big picture.  And, focusing on getting rid of OR may provide more gain.

If you have a million rows in the table, then this formulation will run much faster:

SELECT .. FROM .. WHERE (StreetNumber = 124 AND StreetName = "Smith" AND StreetSuffix = "Rd")
UNION ALL
SELECT .. FROM .. WHERE (StreetNumber = 142 AND StreetName = "Jones" AND StreetSuffix = "St")
UNION ALL
SELECT .. FROM .. WHERE (StreetNumber = 8190 AND StreetName = "Smith" AND StreetSuffix = "Ln")
...


but there are issues with such.  You will need an index:

INDEX(StreetNumber, StreetName, StreetSuffix)  -- (in any order)


If there might be duplicates, then use UNION DISTINCT instead of UNION ALL.

For up to a few hundred rows, just write it whatever is simplest for you (eg, OR without optimization).

You will soon find that your model for addresses is incomplete.  What about "Apt. 123" or "c/o ...".  Etc.  There is probably no good reason to split an address into multiple columns -- simply have a single string with all the parts written in a way that facilitates displaying.  Eliminating the AND would be a tiny optimization.