MySQL Hangs is Simple Query
Hi All,
I am running MySql 4.1 in a widows 2000 server machine. MySql hangs for the following query. When I run the same query against MS sql server database it gives results in mili seconds. Is there is any thing I need to configure in the data base to run this query.(Query and table creation scripts are given bellow).
I am really surprised that MySql hangs on such a simple query. I only have 100 rows in my Eobjects table and 1000 rows in EatrributeValues table. MS Sql server responses to the same query in milliseconds. Are there any configurations required to optimize MySql for queries specially join queries.
At the first glance it seems to me like MySql is taking the Cartesian product when joins are processed. SO my understanding is that MySql is going to process 100 * 1000 * 1000 * 1000 records to process this query. ( I came to this conclusion since I can make the query to fail with out getting hung by setting the max_join_querysize to 10000000.)
Query
-------------------------------------------------------------------------------------------------------------
SELECT DISTINCT A0.class_package_name,A0.class_package_ns_uri,A0.class_name,A0.container_repository_id,A0.repository_id FROM EObject A0 INNER JOIN EAttributeValue A1 ON A0.repository_id=A1.container_repository_id INNER JOIN EAttributeValue A2 ON A0.repository_id=A2.container_repository_id INNER JOIN EAttributeValue A3 ON A0.repository_id=A3.container_repository_id WHERE ( ( A0.class_package_name = 'EditServ') AND (A0.class_name = 'JobDefn')) AND ((A1.feature_id = 31) AND A1.string_value = 'Mike' OR ((A2.feature_id = 31) AND A2.string_value LIKE 'aaa') OR ((A3.feature_id = 14) AND A3.string_value LIKE 'V%'))
Table creation scripts
CREATE TABLE EObject (
repository_id VARCHAR (64) NOT NULL,
class_name VARCHAR (250) NOT NULL ,
class_package_name VARCHAR (250) ,
class_package_ns_uri VARCHAR (250),
container_repository_id VARCHAR (64) , CONSTRAINT EObject_PK PRIMARY KEY ( repository_id) )
CREATE TABLE EAttributeValue(
database_id VARCHAR (255) NOT NULL ,
container_repository_id VARCHAR (64) NOT NULL ,
feature_id INT NOT NULL ,
attribute_name VARCHAR (250) NOT NULL ,
attribute_class_name VARCHAR (250) NOT NULL ,
attribute_class_package_name VARCHAR (250) NOT NULL ,
attribute_class_package_ns_uri VARCHAR (250) NULL ,
data_type INT NOT NULL ,
numeric_value NUMERIC(38, 7) ,
string_value VARCHAR(250) ,
date_value NUMERIC(38, 7) ,
clob_value blob ,
blob_value blob,CONSTRAINT EAttributeValue_PK PRIMARY KEY (database_id) )