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...
web5dev7Asked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
My bad.  Copy paste error in the sql. Try this:

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

0
 
_agx_Commented:
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)  

0
 
_agx_Commented:
> 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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
web5dev7Author Commented:
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 ?  
0
 
_agx_Commented:
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



0
 
web5dev7Author Commented:
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
0
 
web5dev7Author Commented:
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?)

0
 
_agx_Commented:
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.  

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_686-PHP-Prevent-SQL-Injection.html
0
 
web5dev7Author Commented:
ok, will do, thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.