I have a large mysql database which is about 10gb large. One of the tables in the database is called
To locate rows that have a
case column value that contains
'06-' (the characters 0 and 6 followed by a dash ...
One option is to use a LIKE comparison operator:
SELECT ... FROM clients t WHERE t.case LIKE '%06-%' ORDER BY ...
The percent sign characters are wildcards in the LIKE comparison, which match any number of characters (zero, one or more.)
MySQL will need to evaluate that condition for every row in the table. MySQL can't make use of an index range scan operation with that.
SELECT ... FROM clients t WHERE t.case LIKE '%15-%' OR t.case LIKE '%16-%' ORDER BY ...
That will evaluate to true for any values that include the sequence of three characters
'15-' or '16-'.
If there's a more standard format for the case column value, where it always starts with six characters representing date,
mmddyy-nnnnn and you only want to match the 5th thru 7th characters, you could use the underscore wildcard character, which matches any one character...
t.case LIKE '____16-%'
Or you could use a SUBSTR function and an equality comparison...
SUBSTR(t.case,5,3) = '15-'
It's also possible to make use of a
REGEXP comparison in place of the
In terms of performance, all of the above approaches are going to need to crank through every row in the table, to evaluate the comparison condition.
If that date value was stored as a separate column, as a DATE datatype, and there was an index with that as the leading column, then MySQL could make effective use of a range scan operation, for a query like this...
WHERE t.casedate >= '2015-01-01' AND t.casedate < '2017-01-01'