Need abit of optimization help

MySQL & MariaDB Other MySQL® Questions
1

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

[B]Quote:[/B]
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.