SELECT 
DATE_FORMAT (account_date, "Y - m - % d % %") 
The FROM 
Table 
WHERE 
The module='2' 
AND is_delete='0' 
AND code='S1006' 
AND DATE_FORMAT (create_date, "Y - m - % d % %")='2019-11-03' 
GROUP BY 
DATE_FORMAT (account_date, "Y - m - % d % %") order by null 

SQL as above, the data volume is too big, around 30 million, want to according to the SQL add a composite index, create_date and account_date field, use the function cannot be added to the composite index?

CodePudding user response:

Y - yes, you can build a more % % m - % d format date field to index

CodePudding user response:

You use the DATE_FORMAT function, so these two fields and indexes, it is no use,

CodePudding user response:

Do not walk the index function are listed

CodePudding user response:

Both date field, using the function index can't walk, can add to other fields in the where clause for index, (code, the module, is_delete), for example, if code repetition rate is small

CodePudding user response:

1: if the code high selectivity can be indexed (code, is_delete, module, create_date, account_date) of all plus create_date is to prevent the back table.

2: can also modify the SQL index (is_delete create_date, code, the module, account_date)
SELECT
DATE_FORMAT (account_date, "Y - m - % d % %")
The FROM
Table
WHERE
The module='2'
AND is_delete='0'
AND code='S1006'
AND create_date & gt; And create_date & lt;='2019-11-03' '2019-11-04'
GROUP BY
DATE_FORMAT (account_date, "Y - m - % d % %") order by null

These two approaches will see which filter data.