MySQL Forums
Forum List  »  Install & Repo

MySQL Hangs is Simple Query
Posted by: Anuruddha Abhayasinghe
Date: August 17, 2004 03:53PM

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) )


Options: ReplyQuote


Subject
Written By
Posted
MySQL Hangs is Simple Query
August 17, 2004 03:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.