MySQL Forums :: Performance :: bad Join Performance


Advanced Search

bad Join Performance
Posted by: leo . ()
Date: October 31, 2011 12:21AM

Hi,

I've got a problem with poor performance on a Join Query.
Iam trying to accomblish a good performance drill down for user given tags.

Which means all records tagged with X also tagged with y,z.


my query runs for over 5mins straight and looks like this:

SELECT name,count(*),tags.tag_id AS 'id'
FROM base
INNER JOIN tags ON base.tag_id = tags.tag_id
INNER JOIN base REV0 USE INDEX(tag_hash) ON REV0.`hash`=base.`hash`
INNER JOIN base REV1 USE INDEX(tag_hash) ON REV1.`hash`=base.`hash`
WHERE
usr_id = $USR_ID
AND REV0.tag_id=$value
AND REV1.tag_id=$value
GROUP BY tags.tag_id


My database schema:

CREATE TABLE `base` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`hash` char(32) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`uid`),
KEY `tag_id` (`tag_id`),
KEY `hash_value` (`hash`),
KEY `tag_hash` (`tag_id`,`hash`),
CONSTRAINT `base_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

CREATE TABLE `tags` (
`tag_id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`usr_id` int(11) NOT NULL,
PRIMARY KEY (`tag_id`),
KEY `usr_id` (`usr_id`),
) ENGINE=InnoDB


is there any tweak a could do to cut down the query time?

Options: ReplyQuote


Subject Views Written By Posted
bad Join Performance 3064 leo . 10/31/2011 12:21AM
Re: bad Join Performance 793 Jørgen Løland 10/31/2011 12:41PM
sry, double post 641 leo mueller 11/01/2011 12:12AM
sry, double post 866 leo mueller 11/01/2011 12:26AM
Re: bad Join Performance 688 leo mueller 11/01/2011 03:33AM
Re: bad Join Performance 759 Jørgen Løland 11/01/2011 08:29AM
Re: bad Join Performance 716 leo mueller 11/02/2011 08:21AM
Re: bad Join Performance 641 Jørgen Løland 11/03/2011 12:56AM
Re: bad Join Performance 659 leo mueller 11/03/2011 12:24AM
Re: bad Join Performance 718 Jørgen Løland 11/04/2011 06:35AM
Re: bad Join Performance 757 Rick James 11/02/2011 06:23AM
Re: bad Join Performance 636 leo mueller 11/02/2011 07:42AM


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.