How to store a lot of products into your PHP/MySQL database?

Discussion in 'Commission Junction' started by markowe, Sep 17, 2009.

0
  1. #1
    Trying to get my head round efficient ways of storing product data from an advertiser in my local database, in PHP/MySQL.

    Say my advertiser has a LOT of products, like 100,000 or more, and I want them in my local database so I'm not calling the API all the time and so I can do other sorting and searching on it.

    Any thoughts on a good model for this? I figure a daily cron-job that will pull the results in from the API, but I am thinking that's going to be a good hunderd API calls if I go that route (max 1000 products per page of results I believe). So if I order a Product Export in XML which I can pull down daily and import, seems like a better bet. But that again seems like a massive overhead - getting a 3-4Mb gzipped file via http, unzipped, that can be 150 Mb! Processing all that from a file on the local server, and populating my database with it, even once a day, sounds like a lot of server time which my host could slap me for.

    First time I have really tried to do something with this much data in PHP/mySQL, so wonder if anyone has any pointers?

    P.S. On shared hosting for the time being...
     
    Last edited: Sep 17, 2009
    markowe, Sep 17, 2009 IP
  2. hanji

    hanji Peon

    Messages:
    612
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Hello

    I would run some local tests to see what the load is like first before crushing the shared hosting server. I think the parsing will be much faster than you think. Also, check to see if the you and the shared host is running mysql-5.1 or 6, there is a great SQL command for this.. 'LOAD XML LOCAL INFILE'

    I wrote a couple of blog posts that could be useful for you.

    FTP’ing files from CJ’s FTP server to your host

    and

    Parsing XML CJ Product Feeds

    HTH
    hanji
     
    hanji, Sep 24, 2009 IP
  3. markowe

    markowe Well-Known Member

    Messages:
    1,136
    Likes Received:
    26
    Best Answers:
    0
    Trophy Points:
    165
    #3
    Thanks for that, your blog posts look to be very much along the lines of what I am looking for! I also found an article about LOAD DATA, which I didn't know about before. Looks like a solution to me - now just to write it :)
     
    markowe, Sep 25, 2009 IP
  4. krishmk

    krishmk Well-Known Member

    Messages:
    1,376
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    185
    #4
    You said you are on shared hosting.
    Most shared hosting servers have a maximum script execution time set to scripts. (most probably 30 seconds).
    You need to take this into consideration and make sure you set a limit for your script to execute. Else the script would break and the work would be incomplete.
     
    krishmk, Sep 25, 2009 IP
  5. markowe

    markowe Well-Known Member

    Messages:
    1,136
    Likes Received:
    26
    Best Answers:
    0
    Trophy Points:
    165
    #5
    P.S. In your second article about FTPing there is a dead link to the first article, just to let you know. Like the blog BTW, very useful, have subscribed to your feed.
     
    markowe, Sep 25, 2009 IP