SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Efficient PHP/MySQL ???

    Hi,

    As some of you may know, I made a mailing list script called MyMail (see signature). I'm starting to add more features to it now and have noticed a very inefficient piece of code. This code loops through emails addresses, then checks if they already exist in the database.

    Goes like this:

    1. Insert into textarea
    2. Explode of line break
    3. Loop through them
    -> Validate Regex Style
    -> Validate in Database
    -> Insert

    If there are 1000 emails, then this creates 2000 maximum mysql queries, and some people have allot more than 1000 emails to insert.

    Basically, I want to know, if i can reduce this to 2 queries? Funny hey? Well, I've reduced it to 1001 by combining the INSERT into table queries. Just need to combine the other 1000.

    Any ideas?

    -Peter

  2. #2
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use REPLACE instead of INSERT.

    REPLACE will replace the current values in the database with what you send, or if the current values don't exist it will insert them.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  3. #3
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm...

    How did you combine 1000 Insert queries into 1? I'm curious.

    Thanks,
    Carl
    Ohai!

  4. #4
    Digital Warrior Renegade's Avatar
    Join Date
    Nov 2000
    Location
    Portland, OR
    Posts
    480
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you select all emails from the DB, and push them into an array? Then when you compare the emails, you can compare them against the array, and not a query everytime. *shrug*

    Hope your server has a lot of memory
    --There's my 1.5 cents, now where is my change!?!?

  5. #5
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think Renegade's idea is a good one...it's a big array, but it's got to be better than the dreaded query-within-a-loop.

  6. #6
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for all the suggestions, I'll try aspen's suggestion first after reading about it, seams exactly like what I need, but I'm not sure. I was thinkng about the array, but that'd be worse than the queries. I did a test on my pc:

    1 Query with 10,000 rows to inserts take about 1 second!
    10000 queries takes about 20 seconds...

    So there is a massive difference. What i did is do:

    INSERT INTO table (field1,field2) SET ('blah', 'blah'), ('blah2','blah2')

  7. #7
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just checked replace syntax and it won't work because the id's can't be changed and they would be if i were to use it.

    Looks like i can just not validate the emails and let mysql force out an error. That'd save allot of queries! (Down to 1)

    Thanks
    -Peter

  8. #8
    SitePoint Enthusiast scottyparks's Avatar
    Join Date
    Apr 2001
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello friend! I was readin MYSQL becuse somebody give me the link 2 days AGO so I haev readed a lot cause i am determined to do it good.

    Sincothers have helped me i try and help yuo too!

    Why not use
    Email VARCHAR(60) NOT NULL,
    UNIQUE KEY Email (Email)

    that way noemails in the row 'Email' will be the same and if they are an ERROR GETS PRINTED! I HOPE i am not wrong and if i am please correct me cauuse i only am learning!!

    Hopefully yuo are helped
    GOD BLESS YOU!

    ps. freddy yuo will probabely correct me but dont be mean again.

  9. #9
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats what I'm using for one version, but i've got two which one isn't set up like this. I'll have to mess around and try to get it to work

    -Peter

  10. #10
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    instead of REPLACE i prefer to use INSERT IGNORE INTO table in most cases. won't screw up your ids.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •