
 use test;
 
CREATE TABLE strings
(
    stringId BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    hash     varchar(32)         NOT NULL ,
    string   varchar(2048)       NOT NULL ,
    PRIMARY KEY (stringId),
    UNIQUE KEY hash_idx (hash)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into strings select seq,seq,seq from seq_1_to_100000;
 
CREATE TABLE IF NOT EXISTS profiles
(
    profileId    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    oldProfileId INT(11) UNSIGNED DEFAULT 0,
    userId       INT(11) UNSIGNED NOT NULL ,
    webId        INT(11) UNSIGNED NOT NULL DEFAULT 0,
    email        VARCHAR(50)      NOT NULL DEFAULT '',
    active       TINYINT(1)       NOT NULL DEFAULT 0,
    createDate   TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY (profileId),
    KEY userId_idx (userId),
    KEY webId_idx (webId),
    KEY oldProfileId (oldProfileId)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into profiles select seq,seq,seq,seq,seq,1,now() from seq_1_to_10000;

 
CREATE TABLE IF NOT EXISTS schedule
(
    scheduleId INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    cron       varchar(16)     NOT NULL DEFAULT '0 0 7 * * *',
    zone       varchar(5)      NOT NULL DEFAULT '+0200',
    PRIMARY KEY (scheduleId)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert ignore into schedule  select seq,seq,seq from seq_1_to_50;

 
CREATE TABLE IF NOT EXISTS subscriptions
(
    subscriptionId INT(11) UNSIGNED    NOT NULL AUTO_INCREMENT,
    scheduleId     INT(3) UNSIGNED     NOT NULL ,
    profileId      INT(11) UNSIGNED    NOT NULL ,
    stringId       BIGINT(20) UNSIGNED NOT NULL ,
    active         TINYINT(3)          NOT NULL DEFAULT 1,
    createDate     TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    platform       TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
    lastAutoId     INT(11) UNSIGNED    NOT NULL DEFAULT 0,
    sendDate       TIMESTAMP,
    PRIMARY KEY (subscriptionId),
     KEY (profileId) , 
     KEY (scheduleId) ,
     KEY (stringId)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
 
insert into subscriptions  select seq,FLOOR(RAND() * 100),seq,seq,1,now(),1,seq,now() from seq_1_to_30000;
insert into subscriptions  select seq,FLOOR(RAND() * 100),seq,seq,0,now(),0,seq,now() from seq_300001_to_60000;


CREATE TABLE IF NOT EXISTS send
(
    subscriptionId INT(11) UNSIGNED NOT NULL,
    next           DATETIME         NOT NULL,
    prev           DATETIME ,
    PRIMARY KEY (subscriptionId),
    KEY next_idx (next),
    KEY (subscriptionId) 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into send  select seq,now(),now() from seq_1_to_100000;

analyze table send,subscriptions,schedule,profiles,strings;

SELECT
   subscriptionId, string, stringId, userId, webId, cron, lastAutoId
  FROM send 
  JOIN subscriptions  s USING(subscriptionId)
  JOIN profiles USING(profileId)
  JOIN strings USING(stringId)
  JOIN schedule USING(scheduleId)
  WHERE next <= NOW()+1
    AND send.subscriptionId IN (134, 499, 422, 680, 750, 112, 114, 132, 836, 132, 101)
    AND platform != 1;
    

analyze SELECT
   subscriptionId, string, stringId, userId, webId, cron, lastAutoId
  FROM send 
  JOIN subscriptions  s USING(subscriptionId)
  JOIN profiles USING(profileId)
  JOIN strings USING(stringId)
  JOIN schedule USING(scheduleId)
  WHERE next <= NOW()+1
    AND send.subscriptionId IN (134, 499, 422, 680, 750, 112, 114, 132, 836, 132, 101)
    AND platform != 1;
