From: Jorge Bastos Date: January 18 2006 3:56pm Subject: Re: query with union is pretty slow - mysql 4.1.15 List-Archive: http://lists.mysql.com/win32/17992 Message-Id: <055901c61c47$b15c74c0$0301a8c0@hercules.decimalint.pt> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit 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" To: 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)