I think I'll throw in the towel. Here is the slow performance I'm getting when joining 13 tables. The query simply takes too long.
J
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 Medias, Organisms, MediaAuthors, MediaTissues, MediaCells, MediaOrganelles,
MediaMacromolecules,
Authors, Techniques, Tissues, Cells, Organelles, Macromolecules, Admin
WHERE Organisms.common_name LIKE '%common%' and Medias.fk_organism_id = Organisms.pk_organism_id and Medias.fk_technique_id =
Techniques.pk_technique_id
and Medias.pk_media_id = MediaAuthors.fk_media_id and MediaAuthors.fk_author_id = Authors.pk_author_id
and Medias.pk_media_id = MediaTissues.fk_media_id and MediaTissues.fk_tissue_id = Tissues.pk_tissue_id
and Medias.pk_media_id = MediaCells.fk_media_id and MediaCells.fk_cell_id = Cells.pk_cell_id
and Medias.pk_media_id = MediaOrganelles.fk_media_id and MediaOrganelles.fk_organelle_id = Organelles.pk_organelle_id
and Medias.pk_media_id = MediaMacromolecules.fk_media_id and MediaMacromolecules.fk_macromolecule_id =
Macromolecules.pk_macromolecule_id and Medias.fk_admin_id = Admin.pk_admin_id
*************************** 1. row ***************************
table: Authors
type: system
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using temporary
*************************** 2. row ***************************
table: MediaCells
type: index
possible_keys: PRIMARY,fk_cell_id
key: PRIMARY
key_len: 16
ref: NULL
rows: 2
Extra: Using index
*************************** 3. row ***************************
table: Tissues
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 4. row ***************************
table: Cells
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: MediaCells.fk_cell_id
rows: 1
Extra:
*************************** 5. row ***************************
table: Organelles
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 6. row ***************************
table: Medias
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: MediaCells.fk_media_id
rows: 1
Extra:
*************************** 7. row ***************************
table: MediaOrganelles
type: eq_ref
possible_keys: PRIMARY,fk_organelle_id
key: PRIMARY
key_len: 16
ref: Medias.pk_media_id,Organelles.pk_organelle_id
rows: 1
Extra: Using index
*************************** 8. row ***************************
table: MediaMacromolecules
type: index
possible_keys: PRIMARY,fk_macromolecule_id
key: PRIMARY
key_len: 16
ref: NULL
rows: 2
Extra: where used; Using index
*************************** 9. row ***************************
table: Techniques
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: Medias.fk_technique_id
rows: 1
Extra: Using index
*************************** 10. row ***************************
table: Organisms
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: Medias.fk_organism_id
rows: 1
Extra: where used
*************************** 11. row ***************************
table: MediaAuthors
type: eq_ref
possible_keys: PRIMARY,fk_author_id
key: PRIMARY
key_len: 12
ref: Medias.pk_media_id,const
rows: 1
Extra: Using index
*************************** 12. row ***************************
table: MediaTissues
type: eq_ref
possible_keys: PRIMARY,fk_tissue_id
key: PRIMARY
key_len: 16
ref: Medias.pk_media_id,Tissues.pk_tissue_id
rows: 1
Extra: Using index
*************************** 13. row ***************************
table: Macromolecules
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: MediaMacromolecules.fk_macromolecule_id
rows: 1
Extra:
*************************** 14. row ***************************
table: Admin
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: Medias.fk_admin_id
rows: 1
Extra: Using index; Distinct
14 rows in set (12.41 sec)
_________________________________________________________________
The new MSN 8: smart spam protection and 3 months FREE*. http://join.msn.com/?page=features/junkmail&xAPID=42&PS=47575&PI=7324&DI=7474&SU= http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_smartspamprotection_3mf
---------------------------------------------------------------------
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
