MySQL Forums :: Performance :: bad Join Performance

Advanced Search

Re: bad Join Performance
Posted by: Jørgen Løland ()
Date: November 01, 2011 08:29AM


* What's the output of these
- select count(distinct usr_id) from tags
- select count(*) from tags
- select count(*) from base
* Does it help to run ANALYZE TABLE on all tables?

I have a feeling that it's possible to optimize the database schema and/or the query fundamentally, but I don't really understand what it's supposed to model so it's hard to suggest something. For example, what's the point with the hash, and do you risk self-joining with rows in base that has the same hash but a different associated usr_id? From what I understand you are interested in usr_ids tagged with x, y and z? If so: what about something like this?

FROM tags tags1 JOIN
tags tags2 ON tags1.usr_id=tags2.usr_id JOIN
WHERE tags1.usr_id=4 AND
tags1.tag_id=75 AND
tags2.tag_id=52 AND
GROUP BY tags1.tag_id

If that's not what you're trying to do you'll have to explain in more detail.

Btw, the base.tag_id index can be deleted since it covers the same as tag_hash does.

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle

Options: ReplyQuote

Subject Views Written By Posted
bad Join Performance 3178 leo . 10/31/2011 12:21AM
Re: bad Join Performance 835 Jørgen Løland 10/31/2011 12:41PM
sry, double post 671 leo mueller 11/01/2011 12:12AM
sry, double post 911 leo mueller 11/01/2011 12:26AM
Re: bad Join Performance 721 leo mueller 11/01/2011 03:33AM
Re: bad Join Performance 822 Jørgen Løland 11/01/2011 08:29AM
Re: bad Join Performance 742 leo mueller 11/02/2011 08:21AM
Re: bad Join Performance 679 Jørgen Løland 11/03/2011 12:56AM
Re: bad Join Performance 695 leo mueller 11/03/2011 12:24AM
Re: bad Join Performance 781 Jørgen Løland 11/04/2011 06:35AM
Re: bad Join Performance 787 Rick James 11/02/2011 06:23AM
Re: bad Join Performance 677 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.