SELECT page_name, SUM(if(ip LIKE '10.%' OR ip LIKE '139.230.%',1,0)) as Internal SUM(if(ip NOT LIKE '10.%' AND ip NOT LIKE '139.230.%',1,0)) as External FROM ip_logs GROUP BY page_name
That should be pretty quick and putting a WHERE condition on it should just making things faster, since the selection is being narrowed and indexes can be used.
On Jun 2, 2004, at 11:35 PM, James KATARSKI wrote:
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]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
