Wikia/app — pull request #1347 by adamkarminski (2013-08-20 13:44:43+00:00)
Title: Change ORDER BY to city_title ASC.

Changing ORDER BY option to city_title ASC removes the problem of semi-random display of wikis. It also improves browsing experience since results are now displayed in alphabethical order. Additionally minor fix of variable's $sPager naming has been done (it threw NOTICE in devbox enviroment).

@SQLReview
1) QUERY
```sql
SELECT city_id,city_title,city_url,city_public,city_dbname FROM `city_variables`,`city_list` WHERE (city_id = cv_city_id) AND (cv_value != 's:0:"";') AND (cv_variable_id = '384') ORDER BY city_title ASC LIMIT 50
```
2) EXPLAIN
```
+----+-------------+----------------+--------+--------------------+---------+---------+--------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+--------------------+---------+---------+--------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | city_variables | ref | PRIMARY,cv_city_id | PRIMARY | 2 | const | 123 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | city_list | eq_ref | PRIMARY | PRIMARY | 4 | wikicities.city_variables.cv_city_id | 1 | |
+----+-------------+----------------+--------+--------------------+---------+---------+--------------------------------------+------+----------------------------------------------+
```

Only change is in the ORDER BY option to city_title ASC. Benefits:
- Wikis are alphabetically sorted and therefore it's easier to browse the list
- city_title column has a MUL key attribute, contrary to the previously used city_sitename.

```
+-------------------------+------------------+------+-----+---------------------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+------------------+------+-----+---------------------------------+----------------+
| city_id | int(9) | NO | PRI | NULL | auto_increment |
| city_path | varchar(255) | NO | | /home/wikicities/cities/notreal | |
| city_dbname | varchar(64) | NO | UNI | notreal | |
| city_sitename | varchar(255) | NO | | wikicities | |
| city_url | varchar(255) | NO | UNI | http://notreal.wikicities.com/ | |
| city_created | datetime | YES | MUL | NULL | |
| city_founding_user | int(5) | YES | MUL | NULL | |
| city_adult | tinyint(1) | YES | | 0 | |
| city_public | int(1) | NO | | 1 | |
| city_additional | text | YES | | NULL | |
| city_description | text | YES | | NULL | |
| city_title | varchar(255) | YES | MUL | NULL | |
| city_founding_email | varchar(255) | YES | | NULL | |
| city_lang | varchar(8) | NO | | en | |
| city_special_config | text | YES | | NULL | |
| city_umbrella | varchar(255) | YES | | NULL | |
| city_ip | varchar(256) | NO | | /usr/wikia/source/wiki | |
| city_google_analytics | varchar(100) | YES | | | |
| city_google_search | varchar(100) | YES | | | |
| city_google_maps | varchar(100) | YES | | | |
| city_indexed_rev | int(8) unsigned | NO | | 1 | |
| city_lastdump_timestamp | varchar(14) | YES | | 19700101000000 | |
| city_factory_timestamp | varchar(14) | YES | | 19700101000000 | |
| city_useshared | tinyint(1) | YES | | 1 | |
| ad_cat | char(4) | NO | | | |
| city_flags | int(10) unsigned | NO | MUL | 0 | |
| city_cluster | varchar(255) | YES | MUL | NULL | |
| city_last_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
| city_founding_ip | int(10) unsigned | NO | MUL | 0 | |
+-------------------------+------------------+------+-----+---------------------------------+----------------+
```