Announcement

Announcement Module
Collapse
No announcement yet.

Need abit of optimization help

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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.


    iptable

    site_id ip 5728 71.225.121.72 3901 85.85.110.190 25587 69.251.1.187 15309 68.90.50.72


    sites

    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
    Trevor

    Quote:




  • #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.

    Comment

    Working...
    X