secondary_indexes.txt

-- XXX: all of this is a bad idea, but it was a nice idea at the time :)

CREATE TABLE IF NOT EXISTS apikey_changed_urls (
    process_minute timestamp,               -- current minute of processing
    apikey text,                            -- apikey
    url text,                               -- url where data changed
    change_time timestamp,                  -- 5-min period where data changed
    process_hour timestamp,                 -- current hour of processing
    process_day timestamp,                  -- current day of processing 
    PRIMARY KEY (process_minute, apikey, url, change_time)); 

CREATE INDEX ON apikey_changed_urls (process_hour);
CREATE INDEX ON apikey_changed_urls (process_day);

INSERT INTO apikey_changed_urls
    (process_minute, process_hour, process_day,
     apikey, url, change_time) 
    VALUES
    ('2014-10-09 08:48', '2014-10-09 08:00', '2014-10-09', 
    'arstechnica.com', 'http://arstechnica.com/', '2014-10-09 08:15');

INSERT INTO apikey_changed_urls
    (process_minute, process_hour, process_day,
     apikey, url, change_time) 
    VALUES
    ('2014-10-09 08:48', '2014-10-09 08:00', '2014-10-09', 
    'arstechnica.com', 'http://arstechnica.com/', '2014-10-09 08:20');

SELECT apikey, url, change_time
    FROM apikey_changed_urls WHERE process_day='2014-10-09';
SELECT apikey, url, change_time
    FROM apikey_changed_urls WHERE process_hour='2014-10-09 08:00';
SELECT apikey, url, change_time 
    FROM apikey_changed_urls WHERE process_minute='2014-10-09 08:48';

-- thanks to apikey-clustering, can also select specific customers to rebuild

SELECT apikey, url, change_time
    FROM apikey_changed_urls WHERE process_hour='2014-10-09 08:00' AND apikey='arstechnica.com';