platform: windows 2000 pro, mysql default table type myIsam, non-binary distribution (install version).
I am still getting very slow query results when I join multiple tables together. I have been trying to figure this out for days and am at a loss. I added an index to my cross reference tables as suggested and things worked very well for the first query but not the second one. Someone thought it might be a bug in the query optimizer. My tables contain only two records and are structured like this:
pk_organism_id---pk_media_id----fk_media_id,fk_author_id---pk_authors_id
fk_organism_id
fk_technique_id
fk_admin_id
pk_technique_id
pk_admin_id
.
.
.
.
.
CREATE TABLE Medias (
pk_media_id bigint unsigned not null auto_increment,
fk_organism_id int unsigned not null,
fk_technique_id int unsigned not null,
fk_admin_id int unsigned not null,
file_name_in_db char(50),
file_name char(50),
file_name_altered char(50),
file_data longblob,
creation_date date,
upload_date date,
download_date date,
modified_date date,
creation_time time,
upload_time time,
update_timestamp timestamp(14),
download_time time,
modified_time time,
pixel_size int unsigned,
file_size int unsigned,
format_in_db char(15),
format char(15),
magnification int unsigned,
label bool,
release bool,
comment varchar(250),
keyword varchar(150),
field_status char(50),
PRIMARY KEY (pk_media_id),
FOREIGN KEY(fk_organism_id) REFERENCES Organisms(pk_organism_id),
FOREIGN KEY(fk_technique_id) REFERENCES Techniques(pk_technique_id),
FOREIGN KEY(fk_admin_id) REFERENCES Admin(pk_admin_id));
CREATE TABLE MediaAuthors (
fk_media_id bigint unsigned not null,
fk_author_id int unsigned not null,
UNIQUE (fk_media_id, fk_author_id),
KEY (fk_author_id),
FOREIGN KEY(fk_media_id) REFERENCES Medias(pk_media_id),
FOREIGN KEY(fk_author_id) REFERENCES Authors(pk_author_id));
CREATE TABLE Authors (
pk_author_id int unsigned not null auto_increment,
admin_username char(50),
last_name char(50),
first_name char(50),
organization varchar(100),
title varchar(100),
position varchar(100),
email char(50),
email_1 char(50),
email_2 char(50),
phone_number char(50),
phone_number_1 char(50),
phone_number_2 char(50),
address varchar(100),
city varchar(100),
province varchar(100),
country varchar(100),
postal_code char(50),
field_status char(50),
PRIMARY KEY(pk_author_id));
mysql> select * from organisms \G;
*************************** 1. row ***************************
pk_organism_id: 1
genus: test genus
species: test species
common_name: test common name
empire: test empire
kingdom: test kingdom
complexity: test nosebleed
comment: test comment
keyword: test keyword
field_status: active
*************************** 2. row ***************************
pk_organism_id: 42
genus: test genus 2
species: test species 2
common_name: test common name 2
empire: Eukaryotic
kingdom: Plant
complexity: Multicellular
comment: test comment 2
keyword: test keyword 2
field_status: active
2 rows in set (0.00 sec)
.
.
.
.
.
////////////// this works well //////////////////////
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;
*************************** 1. row ***************************
table: Macromolecules
type: const
possible_keys: PRIMARY,pk_macromolecule_id
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra: Using temporary
*************************** 2. row ***************************
table: MediaMacromolecules
type: ref
possible_keys: fk_media_id,fk_macromolecule_id
key: fk_macromolecule_id
key_len: 8
ref: const
rows: 1
Extra: where used
*************************** 3. row ***************************
table: Medias
type: eq_ref
possible_keys: PRIMARY,pk_media_id
key: PRIMARY
key_len: 8
ref: MediaMacromolecules.fk_media_id
rows: 1
Extra:
*************************** 4. row ***************************
table: MediaAuthors
type: ref
possible_keys: fk_media_id,fk_author_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 5. row ***************************
table: Authors
type: eq_ref
possible_keys: PRIMARY,pk_author_id
key: PRIMARY
key_len: 4
ref: MediaAuthors.fk_author_id
rows: 1
Extra:
*************************** 6. row ***************************
table: MediaTissues
type: ref
possible_keys: fk_media_id,fk_tissue_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 7. row ***************************
table: Tissues
type: eq_ref
possible_keys: PRIMARY,pk_tissue_id
key: PRIMARY
key_len: 8
ref: MediaTissues.fk_tissue_id
rows: 1
Extra:
*************************** 8. row ***************************
table: MediaCells
type: ref
possible_keys: fk_media_id,fk_cell_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 9. row ***************************
table: Cells
type: eq_ref
possible_keys: PRIMARY,pk_cell_id
key: PRIMARY
key_len: 8
ref: MediaCells.fk_cell_id
rows: 1
Extra:
*************************** 10. row ***************************
table: MediaOrganelles
type: ref
possible_keys: fk_media_id,fk_organelle_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 11. row ***************************
table: Organelles
type: eq_ref
possible_keys: PRIMARY,pk_organelle_id
key: PRIMARY
key_len: 8
ref: MediaOrganelles.fk_organelle_id
rows: 1
Extra:
*************************** 12. row ***************************
table: Organisms
type: eq_ref
possible_keys: PRIMARY,pk_organism_id
key: PRIMARY
key_len: 4
ref: Medias.fk_organism_id
rows: 1
Extra:
*************************** 13. row ***************************
table: Techniques
type: eq_ref
possible_keys: PRIMARY,pk_technique_id
key: PRIMARY
key_len: 4
ref: Medias.fk_technique_id
rows: 1
Extra: Using index; Distinct
*************************** 14. row ***************************
table: Admin
type: eq_ref
possible_keys: PRIMARY,pk_admin_id
key: PRIMARY
key_len: 4
ref: Medias.fk_admin_id
rows: 1
Extra: Using index; Distinct
14 rows in set (0.01 sec)
////////////////////////// this takes over 2 minutes//////////////
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 Organisms.common_name LIKE '%t%'
*************************** 1. row ***************************
table: Macromolecules
type: ALL
possible_keys: PRIMARY,pk_macromolecule_id
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using temporary
*************************** 2. row ***************************
table: Authors
type: ALL
possible_keys: PRIMARY,pk_author_id
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 3. row ***************************
table: MediaTissues
type: index
possible_keys: fk_media_id,fk_tissue_id
key: fk_media_id
key_len: 16
ref: NULL
rows: 2
Extra: Using index
*************************** 4. row ***************************
table: Medias
type: eq_ref
possible_keys: PRIMARY,pk_media_id
key: PRIMARY
key_len: 8
ref: MediaTissues.fk_media_id
rows: 1
Extra:
*************************** 5. row ***************************
table: MediaMacromolecules
type: eq_ref
possible_keys: fk_media_id,fk_macromolecule_id
key: fk_media_id
key_len: 16
ref: Medias.pk_media_id,Macromolecules.pk_macromolecule_id
rows: 1
Extra: Using index
*************************** 6. row ***************************
table: MediaAuthors
type: eq_ref
possible_keys: fk_media_id,fk_author_id
key: fk_media_id
key_len: 12
ref: Medias.pk_media_id,Authors.pk_author_id
rows: 1
Extra: Using index
*************************** 7. row ***************************
table: Tissues
type: eq_ref
possible_keys: PRIMARY,pk_tissue_id
key: PRIMARY
key_len: 8
ref: MediaTissues.fk_tissue_id
rows: 1
Extra:
*************************** 8. row ***************************
table: MediaCells
type: index
possible_keys: fk_media_id,fk_cell_id
key: fk_media_id
key_len: 16
ref: NULL
rows: 2
Extra: where used; Using index
*************************** 9. row ***************************
table: Cells
type: eq_ref
possible_keys: PRIMARY,pk_cell_id
key: PRIMARY
key_len: 8
ref: MediaCells.fk_cell_id
rows: 1
Extra:
*************************** 10. row ***************************
table: MediaOrganelles
type: index
possible_keys: fk_media_id,fk_organelle_id
key: fk_media_id
key_len: 16
ref: NULL
rows: 2
Extra: where used; Using index
*************************** 11. row ***************************
table: Organelles
type: eq_ref
possible_keys: PRIMARY,pk_organelle_id
key: PRIMARY
key_len: 8
ref: MediaOrganelles.fk_organelle_id
rows: 1
Extra:
*************************** 12. row ***************************
table: Organisms
type: eq_ref
possible_keys: PRIMARY,pk_organism_id
key: PRIMARY
key_len: 4
ref: Medias.fk_organism_id
rows: 1
Extra: where used
*************************** 13. row ***************************
table: Techniques
type: eq_ref
possible_keys: PRIMARY,pk_technique_id
key: PRIMARY
key_len: 4
ref: Medias.fk_technique_id
rows: 1
Extra: Using index; Distinct
*************************** 14. row ***************************
table: Admin
type: eq_ref
possible_keys: PRIMARY,pk_admin_id
key: PRIMARY
key_len: 4
ref: Medias.fk_admin_id
rows: 1
Extra: Using index; Distinct
14 rows in set (2 min 22.04 sec)
////////////////////////// this also takes over 2 minutes ///////////
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 Macromolecules.pk_macromolecule_id = MediaMacromolecules.fk_macromolecule_id
INNER JOIN Medias ON MediaMacromolecules.fk_media_id = Medias.pk_media_id
INNER JOIN MediaAuthors ON Medias.pk_media_id = MediaAuthors.fk_media_id
INNER JOIN Authors ON MediaAuthors.fk_author_id = Authors.pk_author_id
INNER JOIN MediaTissues ON Medias.pk_media_id = MediaTissues.fk_media_id
INNER JOIN Tissues ON MediaTissues.fk_tissue_id = Tissues.pk_tissue_id
INNER JOIN MediaCells ON Medias.pk_media_id = MediaCells.fk_media_id
INNER JOIN Cells ON MediaCells.fk_cell_id = Cells.pk_cell_id
INNER JOIN MediaOrganelles ON Medias.pk_media_id = MediaOrganelles.fk_media_id
INNER JOIN Organelles ON MediaOrganelles.fk_organelle_id = Organelles.pk_organelle_id
INNER JOIN Organisms ON Medias.fk_organism_id = Organisms.pk_organism_id
INNER JOIN Techniques ON Medias.fk_technique_id = Techniques.pk_technique_id
INNER JOIN Admin ON Medias.fk_admin_id = Admin.pk_admin_id
WHERE Organisms.common_name LIKE '%t%';
*************************** 1. row ***************************
table: Macromolecules
type: ALL
possible_keys: PRIMARY,pk_macromolecule_id
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using temporary
*************************** 2. row ***************************
table: Authors
type: ALL
possible_keys: PRIMARY,pk_author_id
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 3. row ***************************
table: MediaTissues
type: index
possible_keys: fk_media_id,fk_tissue_id
key: fk_media_id
key_len: 16
ref: NULL
rows: 2
Extra: Using index
*************************** 4. row ***************************
table: Medias
type: eq_ref
possible_keys: PRIMARY,pk_media_id
key: PRIMARY
key_len: 8
ref: MediaTissues.fk_media_id
rows: 1
Extra:
*************************** 5. row ***************************
table: MediaMacromolecules
type: eq_ref
possible_keys: fk_media_id,fk_macromolecule_id
key: fk_media_id
key_len: 16
ref: Medias.pk_media_id,Macromolecules.pk_macromolecule_id
rows: 1
Extra: Using index
*************************** 6. row ***************************
table: MediaAuthors
type: eq_ref
possible_keys: fk_media_id,fk_author_id
key: fk_media_id
key_len: 12
ref: Medias.pk_media_id,Authors.pk_author_id
rows: 1
Extra: Using index
*************************** 7. row ***************************
table: Tissues
type: eq_ref
possible_keys: PRIMARY,pk_tissue_id
key: PRIMARY
key_len: 8
ref: MediaTissues.fk_tissue_id
rows: 1
Extra:
*************************** 8. row ***************************
table: MediaCells
type: index
possible_keys: fk_media_id,fk_cell_id
key: fk_media_id
key_len: 16
ref: NULL
rows: 2
Extra: where used; Using index
*************************** 9. row ***************************
table: Cells
type: eq_ref
possible_keys: PRIMARY,pk_cell_id
key: PRIMARY
key_len: 8
ref: MediaCells.fk_cell_id
rows: 1
Extra:
*************************** 10. row ***************************
table: MediaOrganelles
type: index
possible_keys: fk_media_id,fk_organelle_id
key: fk_media_id
key_len: 16
ref: NULL
rows: 2
Extra: where used; Using index
*************************** 11. row ***************************
table: Organelles
type: eq_ref
possible_keys: PRIMARY,pk_organelle_id
key: PRIMARY
key_len: 8
ref: MediaOrganelles.fk_organelle_id
rows: 1
Extra:
*************************** 12. row ***************************
table: Organisms
type: eq_ref
possible_keys: PRIMARY,pk_organism_id
key: PRIMARY
key_len: 4
ref: Medias.fk_organism_id
rows: 1
Extra: where used
*************************** 13. row ***************************
table: Techniques
type: eq_ref
possible_keys: PRIMARY,pk_technique_id
key: PRIMARY
key_len: 4
ref: Medias.fk_technique_id
rows: 1
Extra: Using index; Distinct
*************************** 14. row ***************************
table: Admin
type: eq_ref
possible_keys: PRIMARY,pk_admin_id
key: PRIMARY
key_len: 4
ref: Medias.fk_admin_id
rows: 1
Extra: Using index; Distinct
14 rows in set (2 min 21.33 sec)
////////////////////////////////////////////////////////////////
//////// this is the code I use in Windows SQL 2000 Server and it works well there,, but not with mySQL /////////////////////////////////
///////////////////////////////////////////////
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 Macromolecules.pk_macromolecule_id = 1 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: Macromolecules
type: const
possible_keys: PRIMARY,pk_macromolecule_id
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra: Using temporary
*************************** 2. row ***************************
table: MediaMacromolecules
type: ref
possible_keys: fk_media_id,fk_macromolecule_id
key: fk_macromolecule_id
key_len: 8
ref: const
rows: 1
Extra: where used
*************************** 3. row ***************************
table: Medias
type: eq_ref
possible_keys: PRIMARY,pk_media_id
key: PRIMARY
key_len: 8
ref: MediaMacromolecules.fk_media_id
rows: 1
Extra:
*************************** 4. row ***************************
table: MediaAuthors
type: ref
possible_keys: fk_media_id,fk_author_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 5. row ***************************
table: MediaTissues
type: ref
possible_keys: fk_media_id,fk_tissue_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 6. row ***************************
table: MediaCells
type: ref
possible_keys: fk_media_id,fk_cell_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 7. row ***************************
table: MediaOrganelles
type: ref
possible_keys: fk_media_id,fk_organelle_id
key: fk_media_id
key_len: 8
ref: Medias.pk_media_id
rows: 1
Extra: Using index
*************************** 8. row ***************************
table: Organisms
type: eq_ref
possible_keys: PRIMARY,pk_organism_id
key: PRIMARY
key_len: 4
ref: Medias.fk_organism_id
rows: 1
Extra:
*************************** 9. row ***************************
table: Authors
type: eq_ref
possible_keys: PRIMARY,pk_author_id
key: PRIMARY
key_len: 4
ref: MediaAuthors.fk_author_id
rows: 1
Extra:
*************************** 10. row ***************************
table: Techniques
type: eq_ref
possible_keys: PRIMARY,pk_technique_id
key: PRIMARY
key_len: 4
ref: Medias.fk_technique_id
rows: 1
Extra: Using index
*************************** 11. row ***************************
table: Tissues
type: eq_ref
possible_keys: PRIMARY,pk_tissue_id
key: PRIMARY
key_len: 8
ref: MediaTissues.fk_tissue_id
rows: 1
Extra:
*************************** 12. row ***************************
table: Cells
type: eq_ref
possible_keys: PRIMARY,pk_cell_id
key: PRIMARY
key_len: 8
ref: MediaCells.fk_cell_id
rows: 1
Extra:
*************************** 13. row ***************************
table: Organelles
type: eq_ref
possible_keys: PRIMARY,pk_organelle_id
key: PRIMARY
key_len: 8
ref: MediaOrganelles.fk_organelle_id
rows: 1
Extra:
*************************** 14. row ***************************
table: Admin
type: eq_ref
possible_keys: PRIMARY,pk_admin_id
key: PRIMARY
key_len: 4
ref: Medias.fk_admin_id
rows: 1
Extra: Using index; Distinct
14 rows in set (10.91 sec)
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail&xAPID=42&PS=47575&PI=7324&DI=7474&SU= http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_addphotos_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
