MySQL Forums
Forum List  »  Performance

(multiple join + subqueries) performence
Posted by: amelie a
Date: November 18, 2008 05:48PM

Hello,

I have a table containing article ids linked to the entity ids :
CREATE TABLE `eid_article_xref` (
`eid` varchar(5),
`aid` varchar(5),
KEY `eid` (`eid`),
KEY `aid` (`aid`)
) ENGINE=MyISAM

insert into eid_article_xref values ('X','a');
insert into eid_article_xref values ('X','b');
insert into eid_article_xref values ('Y','a');
insert into eid_article_xref values ('Z','b');
insert into eid_article_xref values ('T','a');
insert into eid_article_xref values ('U','a');

mysql> select * from eid_article_xref;
+------+------+
| eid  | aid  |
+------+------+
| X    | a    |
| X    | b    |
| Y    | a    |
| Z    | b    |
| T    | a    |
| U    | a    |
+------+------+
6 rows in set (0.00 sec)

I want to know the list of eid that share the same aid as (eid='X' and eid='Y') or same as (eid='X') or same as (eid='Y'), as well as the number of aid that they share.


This is the result I need:
+------+------+------+-----------+
| eid1 | eid2 | eid3 | count(aid)|
+------+------+------+-----------+
| X    |   Y  |   T  | 1         |
| X    |   Y  |   U  | 1         |
| X    |      |   Z  | 1         |
+------+------+------+-----------+
I found how to get these results, but I would like to improve it.

My current way to acheive this is to create a table, and insert rows from 3 different queries.

Here are the steps:

#1. Create a table that will hold the results. use unique key to get rid of unwanted rows:
create table associations 
(eid1 varchar(5), eid2 varchar(5), eid3 varchar(5), count integer,
UNIQUE INDEX(eid1, eid3),  UNIQUE INDEX(eid2, eid3));

#2. Get list of eid sharing same aid as eid=X and eid=Y
insert ignore  into associations 
SELECT a.eid as eid1, b.eid as eid2, c.eid as eid3, count(distinct(a.aid)) as count
FROM eid_article_xref a LEFT JOIN eid_article_xref b on a.aid=b.aid and a.eid='X' and b.eid='Y'
JOIN eid_article_xref c ON b.aid=c.aid
WHERE a.eid <> b.eid AND b.eid <> c.eid AND a.eid <> c.eid
GROUP BY c.eid



#3. Get list of eid sharing same aid as eid=X
insert ignore into associations 
SELECT a.eid as eid1,'' as eid2,  c.eid as eid3, count(distinct(a.aid)) as count
FROM eid_article_xref a 
JOIN eid_article_xref c ON a.aid=c.aid and  a.eid='X'  
WHERE a.eid <> c.eid  and a.eid <> 'Y' and c.eid <> 'Y'    and c.eid not in  (select eid3 from associations where eid2='X')
GROUP BY c.eid


#4. Get list of eid sharing same aid as eid=Y
insert ignore  into associations 
SELECT a.eid as eid1, '' as eid2, c.eid as eid3, count(distinct(a.aid)) as count
FROM eid_article_xref a 
JOIN eid_article_xref c ON a.aid=c.aid and  a.eid='Y' 
WHERE a.eid <> c.eid  and a.eid <> 'X' and c.eid <> 'X' and c.eid not in (select eid3 from associations where eid2='Y')
GROUP BY c.eid


#4. And here are the results I want !!
 select * from associations;
+------+------+------+-------+
| eid1 | eid2 | eid3 | count |
+------+------+------+-------+
| X    | Y    | T    |     1 |
| X    | Y    | U    |     1 |
| X    |      | Z    |     1 |
+------+------+------+-------+
3 rows in set (0.00 sec)

#Delete the table (to not generate too many of these tables if the process is repeted several times)
drop table associations;

... so, it works, but I am sure there is a better (more efficient and simpler) way to do all this!!


Thank you very much for you help!



Edited 1 time(s). Last edit at 11/19/2008 02:26AM by amelie a.

Options: ReplyQuote


Subject
Views
Written By
Posted
(multiple join + subqueries) performence
3267
November 18, 2008 05:48PM


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.