I'm trying to run that query but it takes way too long (more than 1sec):
SELECT area,COUNT(*) AS C FROM t_logs,t_domains WHERE (hostname <> ip) AND LCASE(SUBSTRING_INDEX(hostname, '.', -1))=domain GROUP BY area ORDER BY C DESC;
+------+-----+
| area | C |
+------+-----+
| UN | 810 |
| EU | 484 |
| AS | 70 |
| AM | 52 |
| GUS | 20 |
| OZ | 4 |
| AF | 3 |
+------+-----+
7 rows in set (1.12 sec)
I got a table, t_logs, that contains a column called 'hostname' that contains the whole hostname and I need to extract the 'net', 'com', 'org',... out of it to compare it to t_domains. There's about 1700 rows in t_logs and it may soon grow up to 5000 or even more. t_domains contains 267 rows. (columns: area, domain)
As I cannot use subselects in mySQL, this query takes way too long!
any ideas?
thanx a lot
Philippo
SELECT area,COUNT(*) AS C FROM t_logs,t_domains WHERE (hostname <> ip) AND LCASE(SUBSTRING_INDEX(hostname, '.', -1))=domain GROUP BY area ORDER BY C DESC;
+------+-----+
| area | C |
+------+-----+
| UN | 810 |
| EU | 484 |
| AS | 70 |
| AM | 52 |
| GUS | 20 |
| OZ | 4 |
| AF | 3 |
+------+-----+
7 rows in set (1.12 sec)
I got a table, t_logs, that contains a column called 'hostname' that contains the whole hostname and I need to extract the 'net', 'com', 'org',... out of it to compare it to t_domains. There's about 1700 rows in t_logs and it may soon grow up to 5000 or even more. t_domains contains 267 rows. (columns: area, domain)
As I cannot use subselects in mySQL, this query takes way too long!
any ideas?
thanx a lot
Philippo