Wikia/app — pull request #3336 by nandy-andy (2014-04-09 15:41:22+00:00)
Title: Lyrics API index updating

We base updating of the index on artists pages. The process works as follow:
1. We run a query MediaWiki `recentchanges` table to get recently changed pages (new pages, edits); we get from database `page_id` and `category`.
2. We filter retrieved data by page category, convert pages which aren't in `Artist` category into their "parent".
3. We run scraper for given collection of artist articles = we update them in Solr index.
4. We delete all documents connected to any of the artists which timestamp is lower than the timestamp of the time when scraping started.

There are two MySQL queries in our scrapers which should be reviewed (@SQLReview)

1. Query to get all artist pages. This query was ran once when we were feeding the Solr index with initial data. It shouldn't be run a lot of times -- only when something really bad happen to Solr index and we'll have to rebuild it.
```sql
SELECT cl_from FROM `categorylinks` WHERE cl_to = 'Artist' ORDER BY cl_from;
```
Explain:
```sh
mysql> explain SELECT cl_from FROM `categorylinks` WHERE cl_to = 'Artist' ORDER BY cl_from;
+----+-------------+---------------+------+-------------------------+--------------+---------+-------+--------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+-------------------------+--------------+---------+-------+--------+------------------------------------------+
| 1 | SIMPLE | categorylinks | ref | cl_timestamp,cl_sortkey | cl_timestamp | 257 | const | 131480 | Using where; Using index; Using filesort |
+----+-------------+---------------+------+-------------------------+--------------+---------+-------+--------+------------------------------------------+
1 row in set (0.02 sec)
```

2. Query to get all recently changed pages with their categories. This query is supposed to be run once per 24 hours by a cron job. The results should stay around `1000` to `10000` rows per run.

```sql
SELECT DISTINCT r.rc_cur_id as id,c.cl_to as category FROM `recentchanges` `r` INNER JOIN `categorylinks` `c` ON ((r.rc_cur_id = c.cl_from AND c.cl_to IN ("Artist", "Album", "Song"))) WHERE (r.rc_cur_id > 0) AND rc_namespace = '0' AND (rc_timestamp between 20140408000000 and 20140408235959) ORDER BY rc_timestamp desc;
```
Explain:
```sh
mysql> explain SELECT DISTINCT r.rc_cur_id as id,c.cl_to as category FROM `recentchanges` `r` INNER JOIN `categorylinks` `c` ON ((r.rc_cur_id = c.cl_from AND c.cl_to IN ("Artist", "Album", "Song"))) WHERE (r.rc_cur_id > 0) AND rc_namespace = '0' AND (rc_timestamp between 20140408000000 and 20140408235959) ORDER BY rc_timestamp desc;
+----+-------------+-------+------+----------------------------------------------------------+--------------------+---------+-----------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------------------------------------+--------------------+---------+-----------------------+--------+----------------------------------------------+
| 1 | SIMPLE | r | ref | rc_timestamp,rc_namespace_title,rc_cur_id,rc_ns_usertext | rc_namespace_title | 4 | const | 574228 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | ref | cl_from,cl_timestamp,cl_sortkey | cl_from | 4 | lyricwiki.r.rc_cur_id | 3 | Using where; Using index |
+----+-------------+-------+------+----------------------------------------------------------+--------------------+---------+-----------------------+--------+----------------------------------------------+
2 rows in set (0.04 sec)
```