select statement hangs for several minutes

sem_db

I'm having a very difficult time figuring out why my select statement hangs for several minutes and seems to consume large amounts of computing power in the process. Sometimes taking 45 seconds to complete the query. This only happens for one sql statement at the moment, but it's very difficult to debug! Any comments? It's very slow.

J.

Elena_Mitovska

Please, write which SELECT statement causes such problems and specify number of records in table(s).
Now it is not enough information to advise you anything.

sem_db

mysql> SELECT Tissues.type FROM
-> Medias, Organisms, Techniques, Admin, MediaAuthors,
-> Authors, MediaTissues, Tissues, MediaCells, Cells,
-> MediaOrganelles, Organelles, MediaMacromolecules, Macromolecules
-> WHERE
-> Medias.fk_organism_id = Organisms.pk_organism_id and
-> Medias.fk_technique_id = Techniques.pk_technique_id and
-> Medias.fk_admin_id = Admin.pk_admin_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
-> Tissues.type = 'Testes';
+--------+
| type |
+--------+
| Testes |
| Testes |
+--------+
2 rows in set (1 min 59.67 sec)

mysql>

sem_db

pk stands for private key in the table and
fk stands for foreign key.

If you would like to see the relations, please ask

sem_db

i have these kind of hook ups:

media mediaAuthors Authors

pk_media_id fk_media_id pk_author_id
fk_author_id

[deleted]

How many rows are there in each of the tables? You are joine a lot of tables, so there are many many possible results to process

Are all the FK columns indexed?

Sxooter

That's quite a few tables to join together, and MySQL's planner probably doesnt' re-order the join order for you.

Have you tried moving parts of your where clause around to see if that affects the performance?

Try putting the last part of the where clause first (the tissuetype='testes' part). Also, try joining the smallest tables to each other first.

And as vince mentioned, make sure all those fields you're joining against are indexed. Are they all int fields by the way? Or did you use text keys? Using text keys can make these kinds of joins very slow.

sem_db

All of my private keys (pk) are integers. I only have two records in each table. AND the same SQL statement works incredibly fast in both ACCESS and SQL Windows 2000 Server. Could it be something to do with incorrect keys values in the tables? I will now attempt all of your suggestions... Thanks for your help

Sxooter

Is there some kind of explain command in mysql? i.e.

explain select ....

that will tell you what the query planner is doing? Can you try this in Postgresql? it's free too, and much more capable of handling large joins like this.

sem_db

It seems to work with ONE record in the database, now i'm trying it with TWO records again. Thanks for the suggestion of using a different database.. i may have to do so.

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

sem_db

Is it possible that
PRIMARY KEY (fk_media_id, fk_author_id)

should be:

UNIQUE (fk_media_id, fk_author_id)

instead???

CREATE TABLE MediaAuthors (
fk_media_id bigint unsigned not null,
fk_author_id int unsigned not null,
PRIMARY KEY (fk_media_id, fk_author_id),
FOREIGN KEY(fk_media_id) REFERENCES Medias(pk_media_id),
FOREIGN KEY(fk_author_id) REFERENCES Authors(pk_author_id));

Sxooter

Wow, the output of explain seems kinda useless in MySQL. Are there other keywords you can add to make it more verbose (like explain analyze in postgresql, which runs the query and tells you how long each piece took.)

Have you tried changing your join order to see if that helps?

It looks a lot like you are heading into terrain that is better served by pgsql's genetic query optmizer, which is made just for such problems.

Also, sub selects can often speed these kinds of queries up, but in MySQL I don't think you can do this kind of thing in a subselect.

see

http://developer.postgresql.org/docs/postgres/queries-table-expressions.html
and
http://developer.postgresql.org/docs/postgres/explicit-joins.html

for more info on joins and such in postgresql.

sem_db

My MySQL database is collapsing like a stack of cards... I have tried everything short of deleting all my tables and rebuilding them. This query with only two records takes almost a minute to execute. See text file for a better formatted output.

sem_db

A query with two records takes 2 minutes and 30 seconds

mysql> SELECT distinct Medias.pk_media_id, Organisms.common_name, Tissues.type A
s tiss_type, Cells.type As cell_type,
-> Organelles.type As org_type, Macromolecules.type As macro_type, Authors.l
ast_name, Authors.organization, Medias.file_name,
->
-> Medias.format, Medias.label FROM Medias, Organisms, MediaAuthors, MediaTi
ssues, MediaCells, MediaOrganelles,
->
-> MediaMacromolecules,
-> Authors, Techniques, Tissues, Cells, Organelles, Macromolecules, Admin
-> WHERE Macromolecules.type = 'DNA' and Medias.fk_organism_id = Organisms.p
k_organism_id and Medias.fk_technique_id = Techniques.pk_technique_id
-> and Medias.pk_media_id = MediaAuthors.fk_media_id and MediaAuthors.fk_aut
hor_id = Authors.pk_author_id
-> and Medias.pk_media_id = MediaTissues.fk_media_id and MediaTissues.fk_tis
sue_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 MediaMacromo
lecules.fk_macromolecule_id =
->
-> Macromolecules.pk_macromolecule_id and Medias.fk_admin_id = Admin.pk_admi
n_id;
+-------------+------------------+-----------+-----------+----------+-----------
-+-----------+----------------------------------------+-----------+--------+----
---+
| pk_media_id | common_name | tiss_type | cell_type | org_type | macro_type
| last_name | organization | file_name | format | lab
el |
+-------------+------------------+-----------+-----------+----------+-----------
-+-----------+----------------------------------------+-----------+--------+----
---+
| 1 | test common name | Testes | Sertoli | Nucleus | DNA
| Drebbin | Willheim Duchalas Bradwurst University | bc1 | jpg |
1 |
+-------------+------------------+-----------+-----------+----------+-----------
-+-----------+----------------------------------------+-----------+--------+----
---+
1 row in set (2 min 36.44 sec)

sem_db

my entity relationship diagram is attached:

sem_db

I will try indexing my tables in hopes this will reduce the query time. I will also try using the JOIN keyword in my table hook up.

[deleted]

As there are only two records, is it possible to post a mysqdump of the database so we can try it on our databases and see what happens?