Hi Doug,
I looked at it again and noticed a relationship that I missed before. You
*do* have loc_countries_lang associated to the other tables through the
loc_countries table. Sorry I missed it last time.
I also noticed that you wrote this from the "bottom up" you started with
the most detailed element, "veg" and added information from its parents and
some of its children to complete your query (Wow! what a good night's sleep
can do to help you think, eh?)
I am going to try to diagram the "dependency" tree of the JOINS in this
query. Problem is I have never done this without graphics so it may look
silly.
veg <-> loc_states <-> loc_districts <-> loc_countries <->
loc_countries_lang
<-> users <-> users_intros
<- veg_titles
<- tech_equip
Since the objects seem related in a geographic hierarchy, let me see what
it looks like if I flip the tree around like:
loc_countries <-> loc_countries_lang
<-> loc_states <-> loc_districts
<-> veg <-> users <-> users_intros
<- veg_titles
<- tech_equip
The top two branches of this tree seems to be driven by the "fact" that
lang_ID = 0. The bottom branch is looking for particular "veg" records.
We could narrow part of our search list (improving our join performance) if
we start with a query like:
CREATE TEMPORARY TABLE tmpStateList
SELECT s.state_id, ctrl.country_name , ctr.nice_country_name
FROM loc_countries ctr
INNER JOIN loc_countries_lang ctrl
ON ctr.country_id = ctrl.country_id
AND ctrl.lang_id =0
INNER JOIN loc_districts d
on d.district_id = s.district_id
and d.lang_id = 0
INNER JOIN loc_states AS s
ON s.district_id = d.district_ID
AND s.lang_ID = 0
This gives us a list of all states that speak language 0 along with their
country_name and nice_country_name. Now we need to get at the veg-based
information in order to get the rest of the data asked for in the original
query. Optionally, you can add an index to the temp table to speed up the
next stage's query (I usually do).
ALTER TABLE tmpStateList add key(state_ID);
SELECT v.veg_name
, v.veg_id
, u.user_id
, u.user_name
, IFNULL( t.title_name, 'Untitled' ) AS title_name
, tsl.country_name
, tsl.nice_country_name
, te.equip_name
, CONCAT( ui.first_name, ' ',ui.last_name ) AS full_name
FROM tmpStateList tsl
INNER JOIN veg AS v
tsl.state_id = v.state_id
INNER JOIN users AS u
ON u.user_id = v.user_id
AND u.acct_status = 'Enabled'
INNER JOIN user_intros AS ui
ON ui.user_id = u.user_id
AND ui.lang_id =0
LEFT JOIN veg_titles AS t
ON t.veg_id = v.veg_id
AND t.lang_id =0
LEFT JOIN tech_equip AS te
ON te.equip_id = v.equip_id
WHERE v.latest_version = 'Y'
AND v.cur_status = 'Active'
ORDER BY v.date_submitted DESC
LIMIT 0 , 10
You could flip the deconstruction/reconstruction process I just thought
through. Create a temp table that contains the state_id and the other
veg-based columns (where state.lang_id=0) then JOIN back to it the
district, country, and country language tables to fill in the rest of the
query.
Sometimes deconstructing a complex, multi-stage join like this one and
analyzing each branch of the join separately, it is possible to further
minimize the number of rows at each stage of the join. It just requires a
little more work as you are, in essence, forcing yourself to think like the
query optimizer. Each stage has the potential to be optimized (like the
joins between veg and the user_xx tables, perhaps?). By breaking a single
larger statement down into smaller, more manageable joins, you can get
significant performance enhancement at the expense of having to hand-code
the additional steps that the optimizer would normally *try* to do for you
automatically. Personally, I have improved the performance of a rather
complex join from about 20 minutes to just about 2 seconds by using this
technique so I know it works if done correctly.
If you could, would you please let me know how the split-up query operates
for you? Thanks in advance.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|---------+---------------------------->
| | "Doug V" |
| | <[EMAIL PROTECTED]>|
| | |
| | 07/09/2004 04:31 |
| | PM |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Fax to:
|
| Subject: Re: How to Speed this Query Up?
|
>--------------------------------------------------------------------------------------------------------------------------------|
Thank you for your detailed response.
>You might get better performance just from using the explicit INNER JOINS
>but I make no assumptions.
I tried INNER JOINS and did not see any difference in speed.
>You may also get better performance if you had
>composite indexes (not just several individual field indexes) on the
tables
>that contain all of the columns used in each JOIN clause. For example you
>could have an index on user_intros with (user_id, lang_id) and the engine
>won't have to read the table to check for either condition as they would
>both be integer values that exist in an index.
I am already using composite indexes for every table with a lang_id field,
like user_intros.
>ALSO NOTE: there is no condition in the ON clause of loc_countries_lang
>that relates that table to any other. This means that for all values in
>the veg table you will need to match one row from the loc_countries_lang
>table that has lang_id=0. If there are more than one languages that match
>that key, you will get multiple sets of matches.
Well, I was thinking, since this table will never really change, and there
only a couple of hundred entries, I should just store this as an array and
get the country name directly from the array. However, when I removed the
loc_countries_lang table from the query, it was still slow, i.e. 3-5
seconds
on production server.
Would changing the LEFT JOIN to an INNER JOIN improve things? It would be
possible to change the logic such that the veg_titles table is used in an
INNER JOIN instead, but when I tried that it was still very slow (3-5
seconds). In this instance the EXPLAIN returned the following:
t ALL veg_lang_id NULL NULL NULL 76001 Using where; Using temporary; Using
filesort
Every other table in the EXPLAIN returned one row with type eq_ref
>Please, let me know if I helped or not, OK?
Yes, thank you for your response, however the respone time is still too
slow. Now I'm thinking that maybe my underlying database structure is not
correct. Am I joining too many tables? Is there anything else I can do
before I try increasing the sort_buffer? Thanks.
_________________________________________________________________
MSN Toolbar provides one-click access to Hotmail from any Web page ? FREE
download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
