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


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.