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 2718 leo . 10/31/2011 12:21AM
Re: bad Join Performance 659 Jørgen Løland 10/31/2011 12:41PM
sry, double post 574 leo mueller 11/01/2011 12:12AM
sry, double post 757 leo mueller 11/01/2011 12:26AM
Re: bad Join Performance 559 leo mueller 11/01/2011 03:33AM
Re: bad Join Performance 593 Jørgen Løland 11/01/2011 08:29AM
Re: bad Join Performance 612 leo mueller 11/02/2011 08:21AM
Re: bad Join Performance 516 Jørgen Løland 11/03/2011 12:56AM
Re: bad Join Performance 558 leo mueller 11/03/2011 12:24AM
Re: bad Join Performance 624 Jørgen Løland 11/04/2011 06:35AM
Re: bad Join Performance 594 Rick James 11/02/2011 06:23AM
Re: bad Join Performance 546 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.