really slow query results --- SOLVED

[Joseph Dietz]

Someone helped me solve the problem with my slow query results!
I was missing the index which I should have added to my cross reference tables.. AND I should have used the INNER JOIN clause instead of joining my table with the WHERE clause. In the future I will be far more careful designing my queries.

Example:
pk = private key
fk = foreign key

each media has multiple authors:

pk_media_id ------ fk_media_id, fk_author_id-------pk_aurthor_id

each media has multiple tissues:

pk_media_id------- fk_media_id, fk_tissue_id-------pk_tissue_id

All I had to do was add an index to the fk_author_id and fk_tissue_id using the following syntax:

ALTER TABLE MediaAuthors ADD KEY (fk_author_id);
ALTER TABLE MediaTissues ADD KEY (fk_tissue_id);




EXPLAIN SELECT DISTINCT Medias.pk_media_id, Organisms.common_name, Tissues.type As tiss_type, Cells.type As cell_type,

Organelles.type As org_type, Macromolecules.type As macro_type,
Authors.last_name, Authors.organization, Medias.file_name, Medias.format, Medias.label
FROM Macromolecules
INNER JOIN MediaMacromolecules ON fk_macromolecule_id = pk_macromolecule_id
INNER JOIN Medias ON pk_media_id = MediaMacromolecules.fk_media_id
INNER JOIN MediaAuthors ON pk_media_id = MediaAuthors.fk_media_id
INNER JOIN Authors ON fk_author_id = pk_author_id
INNER JOIN MediaTissues ON pk_media_id = MediaTissues.fk_media_id
INNER JOIN Tissues ON fk_tissue_id = pk_tissue_id
INNER JOIN MediaCells ON pk_media_id = MediaCells.fk_media_id
INNER JOIN Cells ON fk_cell_id = pk_cell_id
INNER JOIN MediaOrganelles ON pk_media_id = MediaOrganelles.fk_media_id
INNER JOIN Organelles ON fk_organelle_id = pk_organelle_id
INNER JOIN Organisms ON fk_organism_id = pk_organism_id
INNER JOIN Techniques ON fk_technique_id = pk_technique_id
INNER JOIN Admin ON Medias.fk_admin_id = pk_admin_id
WHERE pk_macromolecule_id = 1

Thank you all! Joseph:):)



_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail


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