NYCPHP Meetup

[Elijah Insua]

mikez,

I would suggest you break this down a bit, for instance instead of running
13000 sql queries, do a simple look up using IN.

For example:

'SELECT * FROM table WHERE email IN ("' . implode('","',$emailsArray) . '")'

Also, selecting columns by name gives a performance boost.
(I'm also guessing that you are going to be performing an action on the
email by its id)

'SELECT email_id FROM table WHERE email in ("' . implode('","',$emailsArray)
. '")'

If this query gets too large, you can always break the loop down into
smaller increments
so instead of processing all of the email addresses at once, you could break
it into 1000
increments.

These techniques combined with the indexing should speed things up for you.

Oh and by the way, if this is running on every page load I would add some
sort of caching routine
so it only runs every 15 minutes or so.

Hope it helps!

-- Elijah Insua


On Mon, Oct 13, 2008 at 11:15 PM, <mikesz at qualityadvantages.com> wrote:

> Hello Joseph,
>
> Tuesday, October 14, 2008, 2:07:01 PM, you wrote:
>
> > First of all you will want to index your database.  I am guessing the
> > query is taking forever due to it not having indexes.
>
> > Also I would suggest that you do this
>
> > $res = ("SELECT * FROM table WHERE email = ' ". email2chk." ' ";
>
> > also I would make sure you have no duplicates in the database by
> > setting a UNIQUE on the email column.
>
> > Doing these things should speed up the site however note that if you
> > check this on every page load it will be resource intensive.
>
> > also what is $email2Chk is it a string or your array of emails?
>
> > Thanks,
> > Joseph Crawford
>
> > On Oct 14, 2008, at 1:57 AM, mikesz at qualityadvantages.com wrote:
>
> >> Hello NYPHP,
> >>
> >>  I have an array that has 13000 email addresses. For now, its in the
> >>  script but will be in a database table eventually. It is a list of
> >>  known bad guys.
> >>
> >>  I have a database table with 9000 records and one of the fields is
> >>  an email address.
> >>
> >>  In PHP I am using a foreach to loop through the array and do a
> >>  database lookup to try to find any matching emails.
> >>
> >>  I am using this query inside the foreach loop:
> >>
> >>        $res = ("SELECT * FROM `table` WHERE `email` = '$email2Chk'");
> >>        $numRows = mysql_num_rows($res);
> >>        if ( !empty($numRows ))
> >>
> >>
> >>  It processes about 12000  addresses in the array and the script
> >> times out.
> >>
> >>  Anyone have a better idea for doing this task?
> >>
> >>  TIA for any help.
> >>
> >> --
> >> Best regards,
> >> mikesz                          mailto:mikesz at qualityadvantages.com
> >>
> >> _______________________________________________
> >> New York PHP Community Talk Mailing List
> >> http://lists.nyphp.org/mailman/listinfo/talk
> >>
> >> NYPHPCon 2006 Presentations Online
> >> http://www.nyphpcon.com
> >>
> >> Show Your Participation in New York PHP
> >> http://www.nyphp.org/show_participation.php
>
> > _______________________________________________
> > New York PHP Community Talk Mailing List
> > http://lists.nyphp.org/mailman/listinfo/talk
>
> > NYPHPCon 2006 Presentations Online
> > http://www.nyphpcon.com
>
> > Show Your Participation in New York PHP
> > http://www.nyphp.org/show_participation.php
>
> > __________ Information from ESET Smart Security, version of virus
> > signature database 3519 (20081013) __________
>
> > The message was checked by ESET Smart Security.
>
> > http://www.eset.com
>
>
> $email2Chk is an array element. I thought it was but email is not
> indexed. Weird, they index a lot of stuff that doesn't matter but
> email is missing....
>
> --
> Best regards,
>  mikesz                            mailto:mikesz at qualityadvantages.com
>
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20081013/baa39dc0/attachment.html>