FAQ.
Statistics
3Queries:
Time needed:
SELECT d1.id, d1.value as mem_type, ms.id, ms.speed, ms.memory_type, dd1.value AS brand_name, dd2.value AS model_name, u.id AS user_id, u.user_login
FROM memoc_dictionary d1
INNER JOIN memoc_scores ms ON d1.id = ms.memory_type
AND ms.speed = (
SELECT max( ms.speed )
FROM memoc_scores ms2
WHERE ms2.memory_type = ms.memory_type
ORDER BY ms2.posting_date DESC )
AND ms.id = (
SELECT ms3.id
FROM memoc_scores ms3
WHERE ms3.memory_type = ms.memory_type
ORDER BY ms3.speed DESC , ms3.posting_date DESC
LIMIT 1 )
INNER JOIN memoc_sticks s ON ms.sticks_id = s.id
INNER JOIN memoc_dictionary dd1 ON s.brand_id = dd1.id
INNER JOIN memoc_dictionary dd2 ON s.model_id = dd2.id
INNER JOIN memoc_users u ON ms.user_id = u.id
WHERE d1.dict_group = 'mem_type'
ORDER BY d1.value ASC ;
select id, user_login, max(score_count) as score_count from
(select u.id, u.user_login, count(s.id) as score_count from memoc_scores s inner join memoc_users u on s.user_id = u.id
where s.approved = 1
group by u.id, u.user_login) as us
group by id, user_login
limit 0, 4
SELECT s.user_id, u.user_login, count( s.id ) AS score_count
FROM memoc_scores s
INNER JOIN memoc_users u ON s.user_id = u.id
WHERE s.position = 1 and s.approved = 1
GROUP BY s.user_id
LIMIT 0, 5