how can I rewrite / optimize the following query to execute faster:
SELECT count( 1 ) AS ct
WHERE email =' firstname.lastname@example.org'
AND substr(submitdate, 1, 10 ) = substr( now( ) , 1, 10 )
I basically want it to search through today's matching records only rather than all matching records. I already created a combined index (ALTER TABLE `myTable`
ADD INDEX `newindex` (`submitdate`, `email`) ) but not sure what to do after that. When I do an EXPLAIN, it seems to be searching all rows with matching email (regardless of date) - rather than just rows with submitdate of today. ??
thanks in advance for your help...