MySQL Forums
Forum List  »  Optimizer & Parser

Help join optimisation
Posted by: Catia Lavalle
Date: September 05, 2007 05:41AM

Hi!
I am rather newby and I need help in optimizing the following query:

select tag, count(*)
from tag left join (taggableobject tagObj, reference ref)
on( ref.FK_tag = tag.PK_Id
and tagObj.PK_Id = ref.FK_object
and tag.tag != "überfahren"
and tagObj.PK_Id in
(select tagObj.PK_Id
from taggableObject tagObj, reference ref, tag
where tagObj.PK_Id = ref.FK_object
and ref.FK_tag = tag.PK_Id
and tag.tag = "überfahren"))
group by tag;

this is the result of explain:

1 PRIMARY tagObj index PRIMARY PRIMARY 8 (null) 3747 Using where; Using index; Using temporary; Using filesort
1 PRIMARY ref ref C_Reference_FK_Tag,C_Reference_FK_Taggableobject C_Reference_FK_Taggableobject 8 tagging.tagObj.PK_id 1
1 PRIMARY tag eq_ref PRIMARY,tag PRIMARY 8 tagging.ref.FK_tag 1 Using where
2 DEPENDENT SUBQUERY tag const PRIMARY,tag tag 8 const 1
2 DEPENDENT SUBQUERY ref ref C_Reference_FK_Tag,C_Reference_FK_Taggableobject C_Reference_FK_Taggableobject 8 func 1 Using where
2 DEPENDENT SUBQUERY tagObj eq_ref PRIMARY PRIMARY 8 func 1 Using where; Using index

and those are the tables settings:

mysql> DESCRIBE taggableobject;
+----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+----------------+
| PK_id | bigint(20) | NO | PRI | NULL | auto_increment |
| objectId | char(41) | NO | UNI | | |
| isPublic | bit(1) | NO | | | |
+----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


mysql> SHOW INDEX FROM taggableobject;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| taggableobject | 0 | PRIMARY | 1 | PK_id | A | 3747 | NULL | NULL | | BTREE | |
| taggableobject | 0 | objectId | 1 | objectId | A | 3747 | NULL | NULL | | BTREE | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)


mysql> DESCRIBE tag;
+----------------+------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+-------------------+----------------+
| PK_id | bigint(20) | NO | PRI | NULL | auto_increment |
| tag | char(255) | NO | UNI | | |
| referenceCount | int(7) | NO | MUL | | |
| blacklisted | bit(1) | NO | | | |
| version | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------------+------------+------+-----+-------------------+----------------+
5 rows in set (0.02 sec)

mysql> DESCRIBE reference;
+-----------+------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+-------------------+----------------+
| PK_id | bigint(20) | NO | PRI | NULL | auto_increment |
| FK_tag | bigint(20) | NO | MUL | | |
| FK_object | bigint(20) | NO | MUL | | |
| userId | char(41) | NO | MUL | | |
| version | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------+------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

mysql> SHOW INDEX FROM tag;
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| tag | 0 | PRIMARY | 1 | PK_id | A | 53 | NULL | NULL | | BTREE | |
| tag | 0 | tag | 1 | tag | A | 53 | 8 | NULL | | BTREE | |
| tag | 1 | refC | 1 | referenceCount | A | 2 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> SHOW INDEX FROM reference;
+-----------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Co
+-----------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---
| reference | 0 | PRIMARY | 1 | PK_id | A | 9385 | NULL | NULL | | BTREE |
| reference | 0 | U_TAG_OBJECT_USERID | 1 | userId | A | 2346 | NULL | NULL | | BTREE |
| reference | 0 | U_TAG_OBJECT_USERID | 2 | FK_object | A | 9385 | NULL | NULL | | BTREE |
| reference | 0 | U_TAG_OBJECT_USERID | 3 | FK_tag | A | 9385 | NULL | NULL | | BTREE |
| reference | 1 | C_Reference_FK_Tag | 1 | FK_tag | A | 75 | NULL | NULL | | BTREE |
| reference | 1 | C_Reference_FK_Taggableobject | 1 | FK_object | A | 9385 | NULL | NULL | | BTREE |
+-----------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---
6 rows in set (0.00 sec)


could you give me an hint, please?

Options: ReplyQuote


Subject
Views
Written By
Posted
Help join optimisation
3244
September 05, 2007 05:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.