MySQL Forums
Forum List  »  Performance

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

Hi,

* 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?

SELECT tags1.name,count(*),tags1.tag_id
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
jorgenloland.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
3211
October 31, 2011 12:21AM
845
October 31, 2011 12:41PM
677
November 01, 2011 12:12AM
924
November 01, 2011 12:26AM
733
November 01, 2011 03:33AM
Re: bad Join Performance
830
November 01, 2011 08:29AM
752
November 02, 2011 08:21AM
688
November 03, 2011 12:56AM
701
November 03, 2011 12:24AM
794
November 04, 2011 06:35AM
798
November 02, 2011 06:23AM
689
November 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.