| Autor | Zpráva | ||
|---|---|---|---|
| Vanama Profil |
Zdravím,
mám složitější SELECT dvou tabulek. Na tabulce analytics_activities_stats mám index (tw_id, date, sharer) a na analytics_profiles mám index tw_id. SELECT s.tw_id, s.sharer, count(*) as activity_count, audience as sharer_followers, p.tw_id as site_id, p.* FROM analytics_activities_stats s JOIN analytics_profiles p ON s.sharer = p.tw_id WHERE s.tw_id = "972651" AND s.sharer != "" AND s.date BETWEEN "2012-05-10" AND "2012-05-24" GROUP BY s.sharer, s.tw_id ORDER BY activity_count DESC LIMIT 10 explain vrací "id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra" "1";"SIMPLE";"s";"range";"sharer_activity_id,date_tw_id_engagement_activity_type,sharer,tw_id-date-sharer";"tw_id-date-sharer";"7";NULL;"49782";"Using where; Using temporary; Using filesort" "1";"SIMPLE";"p";"eq_ref";"tw_id,tw_id_followers";"tw_id";"4";"czechdb.s.sharer";"1";"Using where" Šlo by s tím něco udělat, aby db nepoužívala Using temporary; Using filesort? Díky za odpovědi. |
||
| Kajman Profil |
#2 · Zasláno: 24. 5. 2012, 16:27:47
Vanama:
„Šlo by s tím něco udělat, aby db nepoužívala Using temporary; Using filesort?“ Možná to nepůjde. Můžete zkusit otestovat SELECT t.*,
p.*
FROM analytics_profiles p
JOIN (SELECT s.tw_id,
s.sharer,
Count(*) AS activity_count
FROM analytics_activities_stats s
WHERE s.tw_id = '972651'
AND s.date BETWEEN '2012-05-10' AND '2012-05-24'
GROUP BY s.tw_id,
s.sharer
HAVING s.sharer != ''
ORDER BY activity_count DESC
LIMIT 10) t
ON t.sharer = p.tw_id
ORDER BY t.activity_count DESC |
||
| Vanama Profil |
#3 · Zasláno: 24. 5. 2012, 17:14:10
Pomohlo to! Je to cca 10x rychlejší což mi už vyhovuje víc. Díky moc!
|
||
|
Časová prodleva: 12 let
|
|||
0