gpsexifbot main query rewrite

SQL

USE commonswiki_p;
SELECT NOW();

SELECT page_title, img_metadata
    FROM page
INNER JOIN image
    ON img_name = page_title
LEFT JOIN templatelinks
    ON tl_from = page_id
    AND tl_namespace = 10
    AND tl_title IN ('Location', 'Location_dec')
WHERE page_namespace = 6
    AND (
        img_metadata LIKE '%"GPSAltitude"%' OR
        img_metadata LIKE '%"GPSLatitudeRef"%'
    )

    -- select specific images (debug)
    -- AND page_title IN (
    --    'Chur_de_la_basilique_Saint-Sauveur,_Dinan,_France.jpg')

    -- go over all uploads of a specific user (manual)
    -- AND img_user_text = "Niteshift"

    -- contains no relevant template
    AND tl_from IS NULL

    -- look at images of the last two days
    AND img_timestamp > DATE_FORMAT(NOW() - INTERVAL 2 DAY, '%Y%m%d%H%i%S')
;

SELECT NOW();

/*
Runtime <10 seconds, usually 1-4 seconds.

+------+-------------+--------------------+--------+-------------------------+---------------+---------+----------------------------------+-------+--------------------------------------+
| id   | select_type | table              | type   | possible_keys           | key           | key_len | ref                              | rows  | Extra                                |
+------+-------------+--------------------+--------+-------------------------+---------------+---------+----------------------------------+-------+--------------------------------------+
|    1 | SIMPLE      | image              | range  | PRIMARY,img_timestamp   | img_timestamp | 16      | NULL                             | 62718 | Using index condition; Using where   |
|    1 | SIMPLE      | image_comment_temp | ref    | PRIMARY,imgcomment_name | PRIMARY       | 257     | commonswiki.image.img_name       |     1 | Using index                          |
|    1 | SIMPLE      | page               | eq_ref | name_title              | name_title    | 261     | const,commonswiki.image.img_name |     1 | Using index                          |
|    1 | SIMPLE      | templatelinks      | ref    | PRIMARY,tl_namespace    | PRIMARY       | 8       | commonswiki.page.page_id,const   |     7 | Using where; Using index; Not exists |
+------+-------------+--------------------+--------+-------------------------+---------------+---------+----------------------------------+-------+--------------------------------------+
*/
SQL

Checking query status...
