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 |

+------+-------------+--------------------+--------+-------------------------+---------------+---------+----------------------------------+-------+--------------------------------------+

*/