• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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...
0
web5dev7
Asked:
web5dev7
  • 5
  • 4
1 Solution
 
_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
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
_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
 
_agx_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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now