Need abit of optimization help

  • Filter
  • Time
  • Show
Clear All
new posts

  • Need abit of optimization help

    Hi all, been reading this blog for a long whole and its great.
    I didnt know there was a forum though )

    Im in need of some help.
    Im tryin to run three mysql queries in one querie not sure if its possible.

    I have two tables, one named sites and one named iptable

    The ip table is rather bigg, around 8 million rows and its growing every week.


    site_id ip 5728 3901 25587 15309


    site_id total_unique unique 5728 500 20 3901 500 20 25587 500 20 15309 500 20

    I tryed doing something like this
    SELECT a.site_id, b.COUNT(*) FROM evots_site a, evots_tracker b WHERE b.site_id = a.site_id

    That didnt work though because of COUNT.

    What im trying to do is

    1. select a site
    2. count IP numbers of the selected site
    3. UPDATE site statistics
    4. Loop to next site

    I have seen queries with both INSERT and SELECT in the same query, i have tryed to get help somewhere else but appearntly not many people knows about it?

    I would appreciate if you guys could help me out abit because all these dumb while loops is killing my server.

    Best Regards


  • #2
    You basically just wrote the syntax wrong with the count:

    SELECT a.site_id , COUNT(b.site_id)FROM evots_site aLEFT JOIN evots_tracker b ON b.site_id = a.site_idGROUP BY a.site_id

    And I also changed to the LEFT JOIN syntax since I suspected that you might want a count for the site even if it is not present in the ip table.