I prefer to use the _explicit_ form of INNER JOIN rather than the
_implicit_ form of the comma-separated list of tables. I feel, with no
proof either way, that by specifying which conditions belong to which JOINs
I gain more detailed control over the query process.
Here is your same query (reformatted *only* so that I made sure I didn't
leave anything out) using explicit INNER JOIN statements:
SELECT v.veg_name
, v.veg_id
, u.user_id
, u.user_name
, IFNULL( t.title_name, 'Untitled' ) AS title_name
, ctrl.country_name
, ctr.nice_country_name
, te.equip_name
, CONCAT( ui.first_name, ' ',ui.last_name ) AS full_name
FROM veg AS v
INNER JOIN loc_countries_lang AS ctrl
ON ctrl.lang_id =0
INNER JOIN loc_countries AS ctr
ON ctr.country_id = ctrl.country_id
INNER JOIN loc_states AS s
ON s.state_id = v.state_id
AND s.lang_id =0
INNER JOIN loc_districts AS d
ON d.district_id = s.district_id
AND d.country_id = ctr.country_id
AND d.lang_id =0
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 might get better performance just from using the explicit INNER JOINS
but I make no assumptions. 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.
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.
Assume for a second that all of the other JOINs have been deleted from the
query. If veg has 100000 rows and there are 200 rows in loc_countries_lang
where lang_id=0 then your WHERE clause will have to be evaluated against
20000000 rows. That may be what you want to do but it's going to take a
while no matter what.
I have an aside to the developers ----
Have you considered expanding the query engine to also use the nonnumeric
values stored in indexes to avoid additional data table reads?? (According
to the docs, the engine would only retrieve a value from the index
(avoiding another table read) if the data is numeric.)
-- end aside
Please, let me know if I helped or not, OK?
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Doug V"
<[EMAIL PROTECTED]> To: [EMAIL PROTECTED]
cc:
07/09/2004 01:51 Fax to:
AM Subject: How to Speed this Query Up?
A query which is constantly being run takes about 3 seconds when not
cached,
and I was wondering if there were any way to speed this up. There are
several tables being joined and sorted by latest date with a LIMIT of 10.
All fields being joined by are indexed. So I'm not sure what else I can do.
The query and EXPLAIN are listed below. Based on the information below, is
there anything I can do to speed this up? There are about 100K rows in the
main veg table.
SELECT v.veg_name, v.veg_id, u.user_id, u.user_name, IFNULL( t.title_name,
'Untitled' ) AS title_name, ctrl.country_name,
ctr.nice_country_name, te.equip_name, CONCAT( ui.first_name, ' ',
ui.last_name ) AS full_name
FROM veg AS v, loc_countries AS ctr, loc_countries_lang AS ctrl,
loc_districts AS d, loc_states AS s, users AS u, user_intros AS ui
LEFT JOIN veg_titles AS t ON v.veg_id = t.veg_id AND t.lang_id =0
LEFT JOIN tech_equip AS te ON v.equip_id = te.equip_id
WHERE d.lang_id =0 AND ctrl.lang_id =0 AND s.lang_id =0 AND ctr.country_id
=
ctrl.country_id AND d.district_id = s.district_id
AND ctr.country_id = d.country_id AND v.latest_version = 'Y' AND
u.acct_status = 'Enabled' AND s.state_id = v.state_id
AND u.user_id = v.user_id AND v.cur_status = 'Active' AND u.user_id =
ui.user_id AND ui.lang_id =0
ORDER BY v.date_submitted DESC
LIMIT 0 , 10
s ALL PRIMARY,district_id NULL NULL NULL 2457 Using where; Using temporary;
Using filesort
d eq_ref PRIMARY,country_id PRIMARY 3 s.district_id,const 1
ctr eq_ref PRIMARY PRIMARY 1 d.country_id 1
ctrl eq_ref PRIMARY,country_id PRIMARY 2 ctr.country_id,const 1
v ref state_id,user_id state_id 2 s.state_id 32 Using where
u eq_ref PRIMARY PRIMARY 2 p.user_id 1 Using where
ui eq_ref uniq_user_lang_id uniq_user_lang_id 3 u.user_id,const 1
t eq_ref veg_lang_id veg_lang_id 4 v.veg_id,const 1
te eq_ref PRIMARY PRIMARY 2 p.equip_id 1
_________________________________________________________________
Is your PC infected? Get a FREE online computer virus scan from McAfeeŽ
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
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]
