asked on

sql

how can I rewrite / optimize the following query to execute faster:

SELECT count( 1 ) AS ct
FROM myTable
WHERE email =' test@test.com'
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...

_agx_

When possible, avoid using functions on columns because it can prevent the db from using your index. But you don't need substr() to find records for a single date. Just use

SELECT count(*) AS ct
FROM myTable
WHERE email =' test@test.com'
WHERE submitdate >= curDate()
AND submitdate < DATE_ADD(curDate(), INTERVAL 1 DAY)

_agx_

> WHERE submitdate >= curDate()
> AND submitdate < DATE_ADD(curDate(), INTERVAL 1 DAY)

ie
WHERE submitdate >= '2011-09-24' --- today at midnight
AND submitdate < '2011-09-25' --- tomorrow at midnight

... which essentially says find all records with today's date

web5dev7

ASKER

ok, but just to clarify, this is part of a php form validation to prevent a user from submitting a form more than once per day - so I wanted it to check for the existence of his email address in all pre-existing records with a submitdate of today. But, for efficiency sake, not have it look at all records, only today's. The dates in the db table are formatted like: 2011-09-24 15:35:15 so we don't want the time to mess it up, right ?

_agx_

Did you test the query? Because time isn't an issue. That's another way of saying find all records with today's date regardless of the time . ie any time between 12 midnight and 11:59:59PM

WHERE submitdate >= '2011-09-24' --- today at midnight
AND submitdate < '2011-09-25' --- tomorrow at midnight

web5dev7

ASKER

phpMyAdmin gives me back the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near:
WHERE submitdate >= curDate()
AND submitdate < DATE_ADD(curD'
at line 4

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

web5dev7

ASKER

great that works in phpMyAdmin. How could I write that in a secure way in my php select statement? This is what phpMyAdmin gave me:

"SELECT count(*) AS ct\n"
"FROM myTable\n"
"WHERE email =\'$email\'\n"
"AND submitdate >= curDate() \n"
"AND submitdate < DATE_ADD(curDate(), INTERVAL 1 DAY)";

Do I have to do something with "mysql_real_escape_string" for security ?
Do I need the \n ? (what are those?)

_agx_

Sorry, I'm not a php expert :( As I think we've answered the original question, it's probably best to open a new thread about the sql injection question.

https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_686-PHP-Prevent-SQL-Injection.html

web5dev7

ASKER

ok, will do, thanks for your help.