query with union is pretty slow - mysql 4.1.15
am 18.01.2006 04:25:21 von Ilavajuthy Palanisamy------_=_NextPart_001_01C61BDE.CD3DFB1E
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi,
=20
MySql 4.1.15 is used. Table type is InnoDB.
=20
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.
=20
mysql> select count(*) from (SELECT policyEvent.id id,
policyEvent.userId userId, policyEvent.entryStatus entryStatus FROM usr
JOIN policyEvent ON usr.id=3DpolicyEvent.userId WHERE =
usr.entryStatus=3D0
AND policyEvent.entryStatus=3D0) t1;
+----------+
| count(*) |
+----------+
| 1677500 |
+----------+
1 row in set (7.63 sec)
=20
mysql> select count(*) from (SELECT id, userId, entryStatus FROM
malwareEvent WHERE entryStatus=3D0 UNION SELECT policyEvent.id id,
policyEvent.userId userId, policyEvent.entryStatus entryStatus FROM usr
JOIN policyEvent ON usr.id=3DpolicyEvent.userId WHERE usr.en
tryStatus=3D0 AND policyEvent.entryStatus=3D0) t1;
+----------+
| count(*) |
+----------+
| 1677500 |
+----------+
1 row in set (47.45 sec)
=20
mysql> select count(id) from malwareevent;
+-----------+
| count(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
=20
mysql> select count(id) from policyevent;
+-----------+
| count(id) |
+-----------+
| 1677500 |
+-----------+
1 row in set (0.72 sec)
=20
mysql> select count(id) from usr;
+-----------+
| count(id) |
+-----------+
| 340 |
+-----------+
1 row in set (0.00 sec)
------_=_NextPart_001_01C61BDE.CD3DFB1E--