Hi All, I'm attempting to generate a report of page hits from both internal and external IP addresses, from one table, using self join.
Some sample data: +--------+-------------+----------------+------------+ | hit_no | page_name | ip | hit_time | +--------+-------------+----------------+------------+ | 6649 | printing | 10.77.1.128 | 1061362239 | | 6650 | wireless | 10.77.28.100 | 1061365331 | | 6651 | after_hours | 10.77.31.101 | 1061365461 | | 6632 | labtimes | 10.77.25.102 | 1061350012 | | 6633 | after_hours | 10.77.25.102 | 1061350017 | | 6634 | labtimes | 10.77.25.102 | 1061350325 | | 6635 | practise | 10.77.30.114 | 1061350609 | | 6636 | support | 10.19.7.155 | 1061352345 | | 6637 | help | 203.35.134.16 | 1061352351 | | 6638 | support | 10.19.7.156 | 1061352352 | | 6639 | support | 10.19.7.151 | 1061352387 | | 6640 | support | 10.19.7.159 | 1061352411 | | 6621 | support | 10.19.7.158 | 1061348961 | | 6620 | support | 10.19.6.112 | 1061348628 | | 7318 | labtimes | 202.137.192.7 | 1063262879 | | 6284 | conditions | 10.77.31.109 | 1060605402 | | 7317 | practise | 202.137.192.7 | 1063262789 | | 7316 | wireless | 203.59.185.185 | 1063262707 | | 7315 | wireless | 10.77.28.121 | 1063256685 | +--------+-------------+----------------+------------+ I'm trying to generate a report like this: (Which I've done in TWO querys, the copied and pasted together) +-------------+----------+----------+ | Page Name | Internal | External | +-------------+----------+----------+ | after_hours | 615 | 105 | | conditions | 332 | 50 | | faq | 89 | 2 | | help | 458 | 174 | | labtimes | 682 | 143 | | support | 2151 | 383 | | passwords | 154 | 22 | | practise | 497 | 99 | | printing | 801 | 85 | | wireless | 926 | 180 | +-------------+----------+----------+ Using a query like this: SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as 'Internal', COUNT(e.page_name) as 'External' FROM ip_logs i, ip_logs e WHERE (i.hit_no != e.hit_no) AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') #Internal Addresses AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') #External Addresses GROUP BY i.page_name; The query takes between 30 seconds and 4.5 minutes to process 6000 rows, depending upon what extra WHERE conditions I put in (like i.hit_no = e.hit_no, or i.page_name = e.page_name etc), the result of which looks like: +-------------+----------+----------+ | Page Name | Internal | External | +-------------+----------+----------+ | after_hours | 638520 | 638520 | | conditions | 353064 | 353064 | | faq | 108924 | 108924 | | help | 355568 | 355568 | | labtimes | 674828 | 674828 | | oracle | 2213536 | 2213536 | | passwords | 165264 | 165264 | | practise | 498296 | 498296 | | printing | 896432 | 896432 | | wireless | 933992 | 933992 | +-------------+----------+----------+ Can anyone offer any suggestions as to the problem with my logic? Regards, James Katarski Systems Administrator School of Computer & Information Science Edith Cowan University ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
