2 messages in com.mysql.lists.win32Re: query with union is pretty slow -...| From | Sent On | Attachments |
|---|---|---|
| Ilavajuthy Palanisamy | 17 Jan 2006 19:24 | |
| Jorge Bastos | 18 Jan 2006 07:55 |
| Subject: | Re: query with union is pretty slow - mysql 4.1.15 |
|---|---|
| From: | Jorge Bastos (mysq...@decimal.pt) |
| Date: | 01/18/2006 07:55:43 AM |
| List: | com.mysql.lists.win32 |
Do some optimizing, in the start of mysql give it --skip-name-resolve there dns lookups help it to more slowlly
----- Original Message ----- From: "Ilavajuthy Palanisamy" <ipal...@consentry.com> To: <win...@lists.mysql.com> Sent: Wednesday, January 18, 2006 3:25 AM Subject: query with union is pretty slow - mysql 4.1.15
Hi,
MySql 4.1.15 is used. Table type is InnoDB.
Query with union is very slow. Can any one suggest workaround or any issue in the written query. The malwareevent table is having 0 records even then the union query is pretty slow.
mysql> select count(*) from (SELECT policyEvent.id id, policyEvent.userId userId, policyEvent.entryStatus entryStatus FROM usr JOIN policyEvent ON usr.id=policyEvent.userId WHERE usr.entryStatus=0 AND policyEvent.entryStatus=0) t1;
+----------+
| count(*) |
+----------+
| 1677500 |
+----------+
1 row in set (7.63 sec)
mysql> select count(*) from (SELECT id, userId, entryStatus FROM malwareEvent WHERE entryStatus=0 UNION SELECT policyEvent.id id,
policyEvent.userId userId, policyEvent.entryStatus entryStatus FROM usr JOIN policyEvent ON usr.id=policyEvent.userId WHERE usr.en
tryStatus=0 AND policyEvent.entryStatus=0) t1;
+----------+
| count(*) |
+----------+
| 1677500 |
+----------+
1 row in set (47.45 sec)
mysql> select count(id) from malwareevent;
+-----------+
| count(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(id) from policyevent;
+-----------+
| count(id) |
+-----------+
| 1677500 |
+-----------+
1 row in set (0.72 sec)
mysql> select count(id) from usr;
+-----------+
| count(id) |
+-----------+
| 340 |
+-----------+
1 row in set (0.00 sec)