List:MySQL on Win32« Previous MessageNext Message »
From:Ilavajuthy Palanisamy Date:January 18 2006 3:25am
Subject:query with union is pretty slow - mysql 4.1.15
View as plain text  
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)


Thread
query with union is pretty slow - mysql 4.1.15Ilavajuthy Palanisamy18 Jan
  • Re: query with union is pretty slow - mysql 4.1.15Jorge Bastos18 Jan