Here are my queries. I have an entity relationship diagram but I don't think I can attach it here! Thank you for all your help. If you would like my entity relationship diagram, please email me.
Sincerely,
Joseph
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
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
| table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
| Macromolecules | const | PRIMARY,pk_macromolecule_id,i_macromolecule_id
| PRIMARY | 8 | const | 1 | Using
temporary |
| MediaCells | index | fk_media_id,i_mediacell_id
| fk_media_id | 16 | NULL | 2 | Using
index |
| Authors | ALL | PRIMARY,pk_author_id,i_author_id
| NULL | NULL | NULL | 2 |
|
| Tissues | ALL | PRIMARY,pk_tissue_id,i_tissue_id
| NULL | NULL | NULL | 2 |
|
| Medias | eq_ref | PRIMARY,pk_media_id,i_media_id
| PRIMARY | 8 | MediaCells.fk_media_id | 1 |
|
| MediaTissues | eq_ref | fk_media_id,i_mediatissue_id
| fk_media_id | 16 | Medias.pk_media_id,Tissues.pk_tissue_id | 1 | Using
index |
| MediaOrganelles | index | fk_media_id,i_mediaorganelle_id
| fk_media_id | 16 | NULL | 2 | where
used; Using index |
| MediaMacromolecules | eq_ref | fk_media_id,i_mediamacromolecule_id
| fk_media_id | 16 | Medias.pk_media_id,const | 1 | Using
index |
| Organisms | eq_ref | PRIMARY,pk_organism_id,i_organism_id
| PRIMARY | 4 | Medias.fk_organism_id | 1 |
|
| Techniques | eq_ref | PRIMARY,pk_technique_id,i_technique_id
| PRIMARY | 4 | Medias.fk_technique_id | 1 | Using
index |
| MediaAuthors | eq_ref | fk_media_id,i_mediaauthor_id
| fk_media_id | 12 | Medias.pk_media_id,Authors.pk_author_id | 1 | Using
index |
| Cells | eq_ref | PRIMARY,pk_cell_id,i_cell_id
| PRIMARY | 8 | MediaCells.fk_cell_id | 1 |
|
| Organelles | eq_ref | PRIMARY,pk_organelle_id,i_organelle_id
| PRIMARY | 8 | MediaOrganelles.fk_organelle_id | 1 |
|
| Admin | eq_ref | PRIMARY,pk_admin_id,i_admin_id
| PRIMARY | 4 | Medias.fk_admin_id | 1 | Using
index; Distinct |
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
14 rows in set (19.55 sec)
////////////////this is better!///////////////////////////
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 = Medias.pk_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
//////////////////////////////////////////////
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
| table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
| Macromolecules | const | PRIMARY,pk_macromolecule_id,i_macromolecule_id
| PRIMARY | 8 | const | 1 | Using
temporary |
| MediaMacromolecules | index | NULL
| fk_media_id | 16 | NULL | 2 | where
used; Using index |
| Authors | ALL | PRIMARY,pk_author_id,i_author_id
| NULL | NULL | NULL | 2 |
|
| MediaTissues | index | fk_media_id,i_mediatissue_id
| fk_media_id | 16 | NULL | 2 | Using
index |
| Medias | eq_ref | PRIMARY,pk_media_id,i_media_id
| PRIMARY | 8 | MediaTissues.fk_media_id | 1 |
|
| MediaAuthors | eq_ref | fk_media_id,i_mediaauthor_id
| fk_media_id | 12 | Medias.pk_media_id,Authors.pk_author_id | 1 | Using
index |
| Tissues | eq_ref | PRIMARY,pk_tissue_id,i_tissue_id
| PRIMARY | 8 | MediaTissues.fk_tissue_id | 1 |
|
| MediaCells | index | fk_media_id,i_mediacell_id
| fk_media_id | 16 | NULL | 2 | where
used; Using index |
| Cells | eq_ref | PRIMARY,pk_cell_id,i_cell_id
| PRIMARY | 8 | MediaCells.fk_cell_id | 1 |
|
| MediaOrganelles | index | fk_media_id,i_mediaorganelle_id
| fk_media_id | 16 | NULL | 2 | where
used; Using index |
| Organelles | eq_ref | PRIMARY,pk_organelle_id,i_organelle_id
| PRIMARY | 8 | MediaOrganelles.fk_organelle_id | 1 |
|
| Organisms | eq_ref | PRIMARY,pk_organism_id,i_organism_id
| PRIMARY | 4 | Medias.fk_organism_id | 1 |
|
| Techniques | eq_ref | PRIMARY,pk_technique_id,i_technique_id
| PRIMARY | 4 | Medias.fk_technique_id | 1 | Using
index; Distinct |
| Admin | eq_ref | PRIMARY,pk_admin_id,i_admin_id
| PRIMARY | 4 | Medias.fk_admin_id | 1 | Using
index; Distinct |
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
14 rows in set (5.14 sec)
///////////// this takes 22.61 seconds//////////////////////
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
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
| table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
| Macromolecules | const | PRIMARY,pk_macromolecule_id,i_macromolecule_id
| PRIMARY | 8 | const | 1 | Using
temporary |
| Authors | ALL | PRIMARY,pk_author_id,i_author_id
| NULL | NULL | NULL | 2 |
|
| Tissues | ALL | PRIMARY,pk_tissue_id,i_tissue_id
| NULL | NULL | NULL | 2 |
|
| MediaCells | index | fk_media_id,i_mediacell_id
| fk_media_id | 16 | NULL | 2 | Using
index |
| Medias | eq_ref | PRIMARY,pk_media_id,i_media_id
| PRIMARY | 8 | MediaCells.fk_media_id | 1 |
|
| MediaTissues | eq_ref | fk_media_id,i_mediatissue_id
| fk_media_id | 16 | Medias.pk_media_id,Tissues.pk_tissue_id | 1 | Using
index |
| MediaMacromolecules | eq_ref | fk_media_id,i_mediamacromolecule_id
| fk_media_id | 16 | Medias.pk_media_id,const | 1 | Using
index |
| MediaAuthors | eq_ref | fk_media_id,i_mediaauthor_id
| fk_media_id | 12 | Medias.pk_media_id,Authors.pk_author_id | 1 | Using
index |
| Cells | eq_ref | PRIMARY,pk_cell_id,i_cell_id
| PRIMARY | 8 | MediaCells.fk_cell_id | 1 |
|
| MediaOrganelles | index | fk_media_id,i_mediaorganelle_id
| fk_media_id | 16 | NULL | 2 | where
used; Using index |
| Organelles | eq_ref | PRIMARY,pk_organelle_id,i_organelle_id
| PRIMARY | 8 | MediaOrganelles.fk_organelle_id | 1 |
|
| Organisms | eq_ref | PRIMARY,pk_organism_id,i_organism_id
| PRIMARY | 4 | Medias.fk_organism_id | 1 |
|
| Techniques | eq_ref | PRIMARY,pk_technique_id,i_technique_id
| PRIMARY | 4 | Medias.fk_technique_id | 1 | Using
index; Distinct |
| Admin | eq_ref | PRIMARY,pk_admin_id,i_admin_id
| PRIMARY | 4 | Medias.fk_admin_id | 1 | Using
index; Distinct |
+---------------------+--------+------------------------------------------------
+-------------+---------+-----------------------------------------+------+------
-------------------+
14 rows in set (22.61 sec)
From: Benjamin Pflugmann <[EMAIL PROTECTED]>
To: Joseph Dietz <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: Severe performace problem linking tables with mysql
Date: Wed, 18 Dec 2002 22:22:00 +0100
Hi.
On Wed 2002-12-18 at 18:40:04 +0000, [EMAIL PROTECTED] wrote:
>
> PLATFORM: 3.23.52-max-nt with Windows 2000 professional (default table type)
>
> I have discovered a performace issue when joining several tables together.
> The performance is extremely poor when performing select queries using the
> WHERE clause and joining the tables with the pk_media_id = fk_media_id
> etc... I guess this is what people might think about when considering using
> mysql. With SQL 2000 Windows Server, and ACCESS, there was no loss in
> performace. 4 of my tables are cross reference tables as such:
Access? You aren't serious, are you? If you really experience
Microsoft Access being faster than MySQL, you are doing something
*seriously* wrong. Probably keys missing or not used for whatever
reason.
Please post the result of EXPLAIN for the query in question and the
result of SHOW INDEX. Also SHOW CREATE TABLE, if posting that
information is OK with you. And whatever information you consider to
be of interest.
Regards,
Benjamin.
--
[EMAIL PROTECTED]
_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
---------------------------------------------------------------------
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
