0

I have a table that contains in the region of 4 million rows. This is searched using a form, and then the results are returned. However, the query seems to take an inexplicably large amount of time, and I'm hoping that there's a more efficient query to run.

SELECT * 
FROM Example 
WHERE STR_TO_DATE(IncorporationDate,'%d/%m/%Y') > '" . $from . "' 
  AND STR_TO_DATE(IncorporationDate,'%d/%m/%Y') < '" . $to . "' 
  AND PostCode REGEXP  '" . $postcode . "' 
  AND `Returns.LastMadeUpDate`='' 
ORDER BY PostCode ASC
  • 1
    Why are you storing dates in strings? Fix that first and you could then add some index to help with the query. – ypercubeᵀᴹ Mar 26 '17 at 10:28
  • @ypercubeᵀᴹ I am only storing dates as strings as the values supplied in the CSV are in the DD/M/YYYY format and not YYYY-MM-DD. I don't believe that there is an easy way around this, especially as the table is updated/replaced every month. Thanks – George Mar 26 '17 at 11:11
  • 2
    Altering a table structure, even a single column, is never easy. Depends how many applications depend on it. You could add another column (of DATE type) and copy/convert the CSV values immediately after import (or during) if you can't change them before. This way your string column will only store the imported data - as they are - and the date column will be indexed and) used in queries. The copy/convert will also validate the values, too. Nothing forbids a nonsense - as a date - value of 45/13/2017 now. – ypercubeᵀᴹ Mar 26 '17 at 11:18
  • @george you can manipulate the date format as you do your data load - easy – Philᵀᴹ Mar 26 '17 at 14:45
  • 2
    @george LOAD DATA INFILE 'myfile.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' (@var1, column2, column3, ...) SET IncorporationDate = STR_TO_DATE(@var1,'%d/%m/%Y') – Philᵀᴹ Mar 26 '17 at 14:50
1

As mentioned in comments (it is really answer for original question) - using function in WHERE - prevent any indexes using.

First of all, You can check - how many of Your queries use form

SELECT * FROM

this is really bad practice with applications, exactly because - if You add new column, some of Your code could be broken. For example:

INSERT into table2 SELECT * FROM table1

If You can fix this (or You not have problems code), You can:

  • convert IncorporationDate to Date format and use this column
  • or easy add 1 more column with proper Date type and use it in queries

But first - You must be sure, all other code will not be broken by new column.

Then You can use one of the tools from answer for Your other question:

now much more similar tools present on market, many of them free in parts cover 99% of life cases. And ETL it more often solution rather than work with build functions only.

  • Clean
  • Transform
  • Load

For answer about next potential question - what with speed? we load tables from 4M to 40M between instances - MySQL, MS SQL, Redshift For "small" we use direct insert from ETL tools, for Big - prepare clean file for load, than direct load (Redshift from S3)

Just add live example - "how it could work"

One of Our scripts - run series of queries with search target records by substring from 2 columns + other parameters. Changing the structure also not possible by the same reasons - a lot of place where need to make changes and no free developers resources. 2 table 3M rows per month.

  • Original time - 7.5 Hours
  • Modified process - prepare with Talend tables for past month with all optimised structure.
  • modification only this script - for exclude substrings from SELECT query, do not touch any other in system. SELECT from prepared tables, write result to same as in original.

Prepare - 5m, script execution - 12m, total time of whole process - 17minutes.

|improve this answer|||||
1

Store dates (or datetimes) using the native MySQL datatypes DATE or DATETIME. That way, you do not need to reformat them when querying. If you need help in modifying IncorporationDate, let us know. After that, I recommend

WHERE IncorporationDate >= '$from'
  AND IncorporationDate  < '$to'

Or, perhaps better (if you know it is a 1-week interval):

WHERE IncorporationDate >= '$from'
  AND IncorporationDate  < '$from' + INTERVAL 7 DAY

For this...

  AND PostCode REGEXP  '" . $postcode . "' 

Why use a complex REGEXP Perhaps one of these will suffice:

  AND PostCode = '$postcode'      -- just equality

  AND PostCode LIKE '$postcode%'  -- user gave only first part

The latter case is especially handy in the US with its 5- and 9-digit zipcodes.

  AND `Returns.LastMadeUpDate`='' 

I hope that is not a date -- Please don't use VARCHAR for dates.

The optimal index will be one of these 'composite' indexes:

INDEX(LastMadeUpDate, PostCode),
INDEX(LastMadeUpDate, IncorporationDate)

Suggest you add both and let the Optimizer pick.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.