Tukang Ronda paling aktif 2017 - hanya artikel

SQL

use idwiki_p;
SELECT SQL_SMALL_RESULT
        CONCAT(
               '[[User:',user_name,'|',user_name,']]'
              ) AS user_name
        ,user_registration
        ,user_editcount
        FROM
              /* Why a subquery? There is no index on user.user_registration (the timestamp of the account creation)
                 so sorting on it is a huge job requiring a complete table scan. On the other hand, user_id loosely
                 tracks the order of the account creation time.

                 But only loosely. Somewhere in the software migrations of the very early days, some accounts came
                 to have higher user_ids than they should have based on their order of creation. It's not many, but
                 it's enough to make the top few entries out of order.
                 
                 So first we sort by user_id (limit 250) in this subquery, which is conveniently low-cost because
                 that is the primary key on the "user" table. Then the outer query sorts those results by
                 user_registration - a trivial job for 250 rows - and limits to 200. The difference is to allow some
                 margin for the uncertain ordering caused by those eccentric old accounts.

                 This version of the query completes in well under a minute. There may be a clever and pure way to
                 avoid all this hacking around with subqueries, but I'm a big believer in "good enough".

                 -- Andrew (Thparkth)
              */
              (
                   SELECT  user_name,user_registration,user_editcount
                   FROM    user
                   WHERE   user_name IN
                   (
                   /* Return a list of all non-IP editors who have hit "save' on any page in the last month */
                        SELECT DISTINCT rc_user_text
                        FROM    recentchanges
                        WHERE   rc_timestamp>date_format(date_sub(NOW(),INTERVAL 30 DAY),'%Y%m%d%H%i%S')
                        AND     rc_user_text NOT REGEXP '^[0-9]{1,3}\\.[0-9]'
                        AND     rc_user_text NOT REGEXP '\\:.+\\:'
                   )
                   AND user_registration IS NOT NULL
                   ORDER BY user_id
                   LIMIT 250
              ) AS InnerQuery
        ORDER BY user_registration
        LIMIT 200;
SQL

Checking query status...
