Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table where translations are stored along with the user_id and when the translation was added. I want to create a query that shows the 'Translator of the month' (the person who did the most translations in that month).

I have a query to get the top translator of all translations:

SELECT user_id, COUNT(user_id) 
FROM gp_translations
WHERE (STATUS='current' OR STATUS='old')
GROUP BY user_id
ORDER BY COUNT(user_id) 

and I have a query that shows the year/month

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month` 
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

but when combining both I fail miserably. The query runs endlessly:

SELECT distinct CONCAT(YEAR(`date_added`),'/', MONTH(`date_added`)) AS `Year/Month`, (
SELECT distinct user_id
FROM gp_translations t2
WHERE (STATUS='current' OR STATUS='old') AND     YEAR(t2.date_added)=YEAR(t1.date_added)
AND MONTH(t2.date_added)=MONTH(t1.date_added)
GROUP BY user_id
ORDER BY COUNT(user_id) DESC
LIMIT 1)
FROM `gp_translations` t1
GROUP BY YEAR(`date_added`), MONTH(`date_added`)
ORDER BY `date_added` DESC

The table gp_translations has the following structure

 CREATE TABLE `gp_translations` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`original_id` INT(10) NULL DEFAULT NULL,
`translation_set_id` INT(10) NULL DEFAULT NULL,
`translation_0` TEXT NOT NULL,
`translation_1` TEXT NULL,
`translation_2` TEXT NULL,
`translation_3` TEXT NULL,
`translation_4` TEXT NULL,
`translation_5` TEXT NULL,
`user_id` INT(10) NULL DEFAULT NULL,
`status` VARCHAR(20) NOT NULL DEFAULT 'waiting',
`date_added` DATETIME NULL DEFAULT NULL,
`date_modified` DATETIME NULL DEFAULT NULL,
`warnings` TEXT NULL,
PRIMARY KEY (`id`),
INDEX `original_id` (`original_id`),
INDEX `user_id` (`user_id`),
INDEX `translation_set_id` (`translation_set_id`),
INDEX `translation_set_id_status` (`translation_set_id`, `status`),
INDEX `date_added` (`date_added`),
INDEX `warnings` (`warnings`(1))
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=342587;

I know I am doing something seriously wrong here. But I can't find the way to properly merge both with decent speed. Any help is appreciated. MySQL version is 5.5.34

share|improve this question

1 Answer 1

Due to lack of windows functions and cte, mysql version is quite verbose, but I hope it will give you desired results in reasonable time (I know it's not fast at all):

SELECT a.*, c.*
FROM 
(
   SELECT yr,mn, MAX(translations_per_month) as max_user_translations
   FROM
   (
     SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, 
     COUNT(1) as translations_per_month
     FROM `gp_translations` t1
     INNER JOIN gp_users u ON 
      (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
     GROUP BY  YEAR(`date_added`), MONTH(`date_added`),user_id
    )b
    GROUP BY yr,mn
)a
INNER JOIN 
(
   SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, display_name,
   COUNT(1) as translations_per_month
   FROM `gp_translations` t1
   INNER JOIN gp_users u ON (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
   GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id, display_name
)c ON  (c.yr = a.yr and c.mn = a.mn and c.translations_per_month = a.max_user_translations)

Also, I made a guess that status column is in gb_users table ; if not, the query needs to be modified a bit.

share|improve this answer
    
Thank you but this query still does not finish. Note: The 'status' field is a field in gp_translations, but that's not the problem. The gp_translation table has about 240k entries. I added mored details on the table and mysql version. –  Deckard Nov 10 '13 at 16:29
    
@Deckard: Can you post result of EXPLAIN for queries? From table definitions I can tell the query will benefit from composite index on gp_translations(user_id,date_added,status) –  a1ex07 Nov 10 '13 at 17:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.