Skip navigation links

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 2703 leo . 10/31/2011 12:21AM
Re: bad Join Performance 655 Jørgen Løland 10/31/2011 12:41PM
sry, double post 573 leo mueller 11/01/2011 12:12AM
sry, double post 756 leo mueller 11/01/2011 12:26AM
Re: bad Join Performance 554 leo mueller 11/01/2011 03:33AM
Re: bad Join Performance 592 Jørgen Løland 11/01/2011 08:29AM
Re: bad Join Performance 608 leo mueller 11/02/2011 08:21AM
Re: bad Join Performance 508 Jørgen Løland 11/03/2011 12:56AM
Re: bad Join Performance 555 leo mueller 11/03/2011 12:24AM
Re: bad Join Performance 621 Jørgen Løland 11/04/2011 06:35AM
Re: bad Join Performance 591 Rick James 11/02/2011 06:23AM
Re: bad Join Performance 544 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.