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?
Subject
Views
Written By
Posted
bad Join Performance
3739
October 31, 2011 12:21AM
1092
October 31, 2011 12:41PM
939
November 01, 2011 12:12AM
1169
November 01, 2011 12:26AM
955
November 01, 2011 03:33AM
1059
November 01, 2011 08:29AM
972
November 02, 2011 08:21AM
892
November 03, 2011 12:56AM
903
November 03, 2011 12:24AM
1038
November 04, 2011 06:35AM
991
November 02, 2011 06:23AM
907
November 02, 2011 07:42AM