Re: query with union is pretty slow - mysql 4.1.15

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" <[email protected]>
To: <[email protected]>
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)




-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]