Skip navigation links

MySQL Forums :: Performance :: bad Join Performance


Advanced Search

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
bad Join Performance 2654 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 745 leo mueller 11/01/2011 12:26AM
Re: bad Join Performance 547 leo mueller 11/01/2011 03:33AM
Re: bad Join Performance 580 Jørgen Løland 11/01/2011 08:29AM
Re: bad Join Performance 601 leo mueller 11/02/2011 08:21AM
Re: bad Join Performance 500 Jørgen Løland 11/03/2011 12:56AM
Re: bad Join Performance 549 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 576 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.