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 2653 leo . 10/31/2011 12:21AM
Re: bad Join Performance 643 Jørgen Løland 10/31/2011 12:41PM
sry, double post 567 leo mueller 11/01/2011 12:12AM
sry, double post 743 leo mueller 11/01/2011 12:26AM
Re: bad Join Performance 547 leo mueller 11/01/2011 03:33AM
Re: bad Join Performance 574 Jørgen Løland 11/01/2011 08:29AM
Re: bad Join Performance 598 leo mueller 11/02/2011 08:21AM
Re: bad Join Performance 497 Jørgen Løland 11/03/2011 12:56AM
Re: bad Join Performance 548 leo mueller 11/03/2011 12:24AM
Re: bad Join Performance 619 Jørgen Løland 11/04/2011 06:35AM
Re: bad Join Performance 575 Rick James 11/02/2011 06:23AM
Re: bad Join Performance 541 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.