You may try increasing the sort buffer size variable since it appears
MySQL is resorting to sorting in a file.
On Jul 9, 2004, at 1:51 AM, Doug V wrote:
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]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
