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
