Your original query (implicit INNER JOINs):
SELECT teu.name, eca.owner_id, ece.value
FROM typed_enterprise_unit teu,
e_contact_association eca,
e_contact_entry ece
WHERE teu.unit_id=eca.owner_id
and eca.entry_id=ece.entry_id
and eca.type_id=68
and (teu.type_path like '%/66/%' or teu.type_id=66)
ORDER BYeca.owner_id
LIMIT 22;
The same query reformatted to demonstrate explicit INNER JOINS
SELECT teu.name
, eca.owner_id
, ece.value
FROM typed_enterprise_unit teu
INNER JOIN e_contact_association eca
ON teu.unit_id=eca.owner_id
INNER JOIN e_contact_entry ece
ON eca.entry_id=ece.entry_id
WHERE eca.type_id=68
AND (teu.type_path like '%/66/%' or teu.type_id=66)
ORDER BYeca.owner_id
LIMIT 22;
I have two immediate suggestions.
1) If you had not attached your information but included it in
your message as text, you would have probably already received an answer.
Next time, please inline your information. Please don't use an attachment
as many people WILL NOT OPEN THEM unless they know you.
2) Be very, very careful when you use a comma delimited list of
tables to imply INNER JOIN that you actually include the necessary join
conditions in your WHERE clause. You did do that this time. However, it is
a well known hazard of that particular query format that those terms can
be accidentally omitted and you can very easily wind up with an
unintentional Cartesian product of your tables.
The explain from your query (extracted from your attachment and included
below) tells me exactly why your query takes so long (heavily trimmed to
avoid excessive message wrapping).
+--+-----------+----------+----+-------------+----+-+------+
|id|select_type|table |type|possible_keys|key | | Extra|
+--+-----------+----------+----+-------------+----+-+------+
| 1| PRIMARY |eca |ALL |NULL |NULL| | Using where; Using
temporary; Using filesort |
| 1| PRIMARY |ece |ALL |NULL |NULL| | Using where|
| 1| PRIMARY |<derived2>|ALL |NULL |NULL| | Using where|
| 2| DERIVED |tp |ALL |NULL |NULL| | |
| 2| DERIVED |eu |ALL |NULL |NULL| | Using where|
+--+-----------+----------+----+-------------+----+-+------------+
Look at the column "possible_keys". Every entry is NULL. That means that
you have no indexes on your tables that could have been used to respond to
this query. This worries me as tables that are involved in relationships
with other tables should at a MINIMUM contain a primary key. Your slow
performance is due to the fact that the query engine had to perform
complete table scans of all tables involved in this query.
May I strongly suggest some reading for you. If you don't understand any
part of it, come back to the list with your questions and we can help
explain it in other ways.
These articles describe ways to implement indexes(keys) in your database
to speed up your queries.
http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html
http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
This article describes how to interpret the output of the EXPLAIN command
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
These articles help to explain how queries are helped by indexes
http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html
http://dev.mysql.com/doc/mysql/en/SELECT_speed.html
There are also MANY articles on the web about query optimization and index
usage. Use your favorite search engine to help you find them. Nearly all
of the principles and techniques that work for the other database engines
(Oracle, MS SQL Server, Informix, etc) will also work for your queries
with MySQL so don't necessarily limit yourself to just MySQL articles.
To solve your speed problem, you need to create an appropriate set of
keys(indexes) on your tables.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Elim Qiu" <[EMAIL PROTECTED]> wrote on 11/18/2004 07:44:01 PM:
> Dear list,
>
> i have some small tables but for some reason the mysql took very long to
> find the results. my query looks
> like below and mysql'e explain is attached for better format. Thanks for
> your help!
>
> select teu.name, eca.owner_id, ece.value
>
> from typed_enterprise_unit teu,
> e_contact_association eca,
> e_contact_entry ece
>
> where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and
> eca.type_id=68 and (teu.type_path like '%/66/%' or
teu.type_id=66)
> and eca.owner_id > 45
>
> order by eca.owner_id limit 50;
> [attachment "need_help_query.txt" deleted by Shawn Green/Unimin] No
> virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.289 / Virus Database: 265.3.1 - Release Date: 11/15/2004
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
