Help with Query Speed

[elgoog]

I have a problem trying to make a query with lots of joins which is slowing down.

 

attriblink

ItemID  |  AttribID

    1      |    1

    1      |    2

    1      |    3

    2      |    1

    2      |    2

    3      |    3

 

With the table above i am using the following query

SELECT distinct(A0.ItemID) FROM attriblink A0 
JOIN attriblink A1 ON A0.ItemID = A1.ItemID AND A1.AttribID = '2'
JOIN attriblink A2 ON A0.ItemID = A2.ItemID AND A2.AttribID = '3'

 

This only brings back record 1, which is the result i need and works really fast up till about 8 joins, then on the 9th slows down a to  >10 seconds

 

Example

SELECT distinct(A0.ItemID) FROM attriblink A0 
JOIN attriblink A1 ON A0.ItemID = A1.ItemID AND A1.AttribID = '21'
JOIN attriblink A2 ON A0.ItemID = A2.ItemID AND A2.AttribID = '49'
JOIN attriblink A3 ON A0.ItemID = A3.ItemID AND A3.AttribID = '14'
JOIN attriblink A4 ON A0.ItemID = A4.ItemID AND A4.AttribID = '90'
JOIN attriblink A5 ON A0.ItemID = A5.ItemID AND A5.AttribID = '59'
JOIN attriblink A6 ON A0.ItemID = A6.ItemID AND A6.AttribID = '55'
JOIN attriblink A7 ON A0.ItemID = A7.ItemID AND A7.AttribID = '1'
JOIN attriblink A8 ON A0.ItemID = A8.ItemID AND A8.AttribID = '66'
JOIN attriblink A9 ON A0.ItemID = A9.ItemID AND A9.AttribID = '23' 

 

In the actual database there are 100,000 rows and there may be 25 attributes selected

is there a faster and better way to run this query?

 

[roopurt18]

What exactly are you trying to accomplish?

[elgoog]

I have a table of items, a table of attributes and a table of attribute links (attriblinks)

 

When a list of 15 attributes are selected, I want to return the list of items that have all of those attributes

 

This list is then used to select all items and all attributes for the list of ItemID's

[roopurt18]

If I understand correctly:

<?php
// THIS OPERATES UNDER THE ASSUMPTION
// `ItemID`,`AttribID` IS UNIQUE!

$attrib_ids = array( 1, 5, 7, 9, 10, 5 ); // Or however many you require
$num_ids = count( $attrib_ids ); // We'll need to know how many there are

// Assert that all input is numeric
foreach( $attrib_ids as $v ) {
    // this is crude, fix according to your needs
    if( ! is_numeric( $v ) ) throw new Exception( 'Trying to trash my database, eh?' );
}

$attrib_ids = implode( ', ', $attrib_ids );
// The following select should return ItemIDs that have ALL of the attributes specified
$select_items_with_all_attribs = "
SELECT
    `ItemID`
FROM `attriblink`
WHERE `AttribID` IN ( {$attrib_ids} )
GROUP BY `ItemdID`
HAVING COUNT(*)={$num_ids}
";

// Now you want to select all items in that list and their attributes
// If you're using a high enough mysql, use a sub-query
$select_final = "
SELECT
    i.*,
    a.*
FROM `attriblink` l
INNER JOIN `Items` i ON l.`ItemdID`=i.`ItemID`
INNER JOIN `Attributes` a ON  l.`AttribID`=a.`AttribID`
WHERE l.`ItemID` IN ({$select_items_with_all_attribs})
";
?>

 

If this doesn't work, post back with your version of MySQL.

[Illusion]

If I understand your requirement correctly

 

select * from ITEMS i inner join  (select itemid from ATTRLINKS a where a.attrid = ALL(select atrributeid from ATTRIBUTES where atrributeid in (attributeList) ) j where j.itemid=i.itemid