List:General Discussion« Previous MessageNext Message »
From:Joseph Dietz Date:December 19 2002 12:36am
Subject:really slow query results --- SOLVED
View as plain text  
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

Thread
really slow query results --- SOLVEDJoseph Dietz19 Dec
  • Re: really slow query results --- SOLVEDDan Nelson19 Dec
  • Re: really slow query results --- SOLVEDHarald Fuchs19 Dec
  • Re: really slow query results --- SOLVEDJocelyn Fournier19 Dec