MySQL Forums
Forum List  »  Performance

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
3211
October 31, 2011 12:21AM
845
October 31, 2011 12:41PM
677
November 01, 2011 12:12AM
924
November 01, 2011 12:26AM
733
November 01, 2011 03:33AM
831
November 01, 2011 08:29AM
752
November 02, 2011 08:21AM
688
November 03, 2011 12:56AM
701
November 03, 2011 12:24AM
794
November 04, 2011 06:35AM
798
November 02, 2011 06:23AM
689
November 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.