Michael,
"Stembridge, Michael" wrote:
>
>
> Finally, I ran an EXPLAIN SELECT on my main query. (fyi, ticketid 1 has a
> noteid of 1)
>
> EXPLAIN SELECT
> call_notes.call_elapsed,
> call_notes.call_seconds,
> call_notes.call_start,
> call_notes.call_end,
> call_notes_text.note_text,
> users.username,
> users.namefirst,
> users.email,
> facility_contact.contactid,
> facility_contact.contact
> FROM
> call_notes,
> call_notes_text,
> users,
> facility_contact
> WHERE
> call_notes.ticketid = '1' &&
> call_notes.userid = users.userid &&
> call_notes.contactid = facility_contact.contactid &&
> call_notes_text.noteid = call_notes.noteid
> ORDER BY
> call_notes.call_start DESC;
>
> +------------------+--------+-------------------------+---------+---------+-
> -----------------------+-------+---------------------------------+
> | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +------------------+--------+-------------------------+---------+---------+-
> -----------------------+-------+---------------------------------+
> | call_notes_text | ALL | PRIMARY,noteid,noteid_2 | NULL | NULL |
> NULL | 14682 | Using temporary; Using filesort |
> | call_notes | eq_ref | PRIMARY,noteid,noteid_2 | PRIMARY | 4 |
> call_notes_text.noteid | 1 | where used |
> | users | eq_ref | PRIMARY,userid | PRIMARY | 4 |
> call_notes.userid | 1 | |
> | facility_contact | eq_ref | PRIMARY,contactid | PRIMARY | 4 |
> call_notes.contactid | 1 | |
> +------------------+--------+-------------------------+---------+---------+-
> -----------------------+-------+---------------------------------+
> 4 rows in set (0.00 sec)
>
> Note the number of rows returned from call_notes_text. This matches the
> total number of rows in that table.
That indicates that MySQL takes call_notes_text as the driving table
(the table to begin with) in your join.
I don't have the experience with MySQL here but in general you should
always ask yourself: how big are the result sets from each table in the
join (considering all your where clauses)?
Then it should be clearer which table would be a good driving table. In
your case it's probably call_notes (assuming that ticket_id is a very
selective column (you mentioned the relationship between note_id and
ticket_id); maybe an index on ticket_id would be helpful too).
You should also query your database to get some idea about the
distribution of the data, e.g. select count(*),ticket_id from call_notes
group by ticket_id.
After you have come to a conclusion you just have to teach MySQL to
process your tables in the appropriate order; rephrase your SQL and use
straight join.
HTH
Regards,
Frank.
--
Dr. Frank Ullrich, Netzwerkadministration
Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625
Hannover
E-Mail: [EMAIL PROTECTED]
Phone: +49 511 5352 587; FAX: +49 511 5352 538
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
