List:General Discussion« Previous MessageNext Message »
From:Nawal Lodha Date:January 12 2004 6:02am
Subject:RE: Query takes 1703.57 sec
View as plain text  
Dear Dan,
Have tried that and it works. 
The query executes in less than 0.1 sec with indexes.
Thanks for the immediate help.
Nawal.


-----Original Message-----
From: Dan Nelson [mailto:dnelson@stripped]
Sent: Monday, January 12, 2004 11:29 AM
To: Nawal Lodha
Cc: mysql@stripped
Subject: Re: Query takes 1703.57 sec


In the last episode (Jan 11), Nawal Lodha said:
> I have a query like - 
> 
> SELECT  AdvElement.c_objectId  ,  AdvElement.c_createdOn  ,
> AdvElement.c_modifiedOn , AdvElement.c_elementType ,
> AdvElement.c_packageId , AdvElement.c_stereoTypeId ,
> AdvElement.c_objectType , AdvElement.c_versionNumber ,
> AdvModelElement.c_details , AdvModelElement.c_status ,
> AdvModelElement.c_name , AdvModelElement.c_desc ,
> AdvInstance.c_classifierId FROM AdvElement , AdvModelElement ,
> AdvInstance , AdvObject WHERE AdvElement.c_objectID =
> AdvModelElement.c_objectID and AdvModelElement.c_objectID =
> AdvInstance.c_objectID and AdvInstance.c_objectID =
> AdvObject.c_objectID and AdvElement.c_objectType = 'AdvObject'.
> 
> The query takes  1703.57 sec to execute.
> 
> The explain on the query gives 
>
> +----+-------------+-----------------+------+---------------+--------+---------+--------+------+-------------+
> | id | select_type | table           | type | possible_keys | key    | key_len | ref 
>   | rows | Extra       |
>
> +----+-------------+-----------------+------+---------------+--------+---------+--------+------+-------------+
> |  1 | SIMPLE      | AdvInstance     | ALL  | [NULL]        | [NULL] |  [NULL] |
> [NULL] |  984 |             |
> |  1 | SIMPLE      | AdvObject       | ALL  | [NULL]        | [NULL] |  [NULL] |
> [NULL] |  982 | Using where |
> |  1 | SIMPLE      | AdvElement      | ALL  | [NULL]        | [NULL] |  [NULL] |
> [NULL] | 1646 | Using where |
> |  1 | SIMPLE      | AdvModelElement | ALL  | [NULL]        | [NULL] |  [NULL] |
> [NULL] | 1418 | Using where |
>
> +----+-------------+-----------------+------+---------------+--------+---------+--------+------+-------------+

Add indexes on all the fields referenced in your WHERE clause.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Query takes 1703.57 secNawal Lodha12 Jan
  • Re: Query takes 1703.57 secDan Nelson12 Jan
RE: Query takes 1703.57 secNawal Lodha12 Jan