wuno wuno - 1 year ago 60
MySQL Question

Searching Large Mysql Database For Exact Date Within Row ID

I have a large mysql database which is about 10gb large. One of the tables in the database is called


In that table there is a colum named


The date this client is created is mixed into the number within this column.

Here is an example of an entry in


The 06 part means this client was created in 2006. I need to pull all the clients that were created in 2015 and 2016. So I need to query for anything that
has a 15 or 16 before the dash.

For example,

Is there a way to do this with only mysql? My thought process was I would have to query the whole column then use php to

I am worried that based on the size of the database this would cause problems.

Answer Source

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:

   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.

   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 LIKE comparison.

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download