

CREATE TABLE `test_country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

CREATE TABLE `test_town` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);


CREATE TABLE `test_ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_town_id` int(11) DEFAULT NULL,
  `test_country_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test_ticket_town1` (`test_town_id`),
  KEY `fk_test_ticket_country1_idx` (`test_country_id`),
  CONSTRAINT `fk_test_ticket_country1_idx` FOREIGN KEY (`test_country_id`) REFERENCES `test_country` (`id`),
  CONSTRAINT `fk_test_ticket_town1` FOREIGN KEY (`test_town_id`) REFERENCES `test_town` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `test_opened_ticket` (
  `id` int(11) NOT NULL,
  `test_town_id` int(11) DEFAULT NULL,
  `test_country_id` int(11) DEFAULT NULL,
  `nb1` int(11) DEFAULT NULL,
  `nb2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_test_opened_ticket_town1` (`test_town_id`),
  KEY `fk_test_opened_ticket_country1_idx` (`test_country_id`),
  CONSTRAINT `fk_test_opened_ticket_ticket1_idx` FOREIGN KEY (`id`) REFERENCES `test_ticket` (`id`),
  CONSTRAINT `fk_test_opened_ticket_country1_idx` FOREIGN KEY (`test_country_id`) REFERENCES `test_country` (`id`),
  CONSTRAINT `fk_test_opened_ticket_town1` FOREIGN KEY (`test_town_id`) REFERENCES `test_town` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);


insert into test_country(id)
SELECT seq FROM seq_1_to_300;

insert into test_town(id)
SELECT seq FROM seq_1_to_20000;

insert into test_ticket(id,test_country_id,test_town_id)
SELECT seq,RAND()*258+1,RAND()*19999+1 FROM seq_1_to_27000000;

insert into test_ticket(test_country_id,test_town_id)
SELECT 186,RAND()*19999+1 FROM seq_1_to_3857690;

insert into test_opened_ticket()
select id, test_town_id,test_country_id ,RAND()*300,RAND()*300 from test_ticket where id % 5 = 0 and test_country_id != 186;


insert into test_opened_ticket()
select id, test_town_id,test_country_id ,RAND()*300,RAND()*300 from test_ticket where id % 5 = 0 and test_country_id = 186;

CREATE INDEX test_opened_ticket_test_town_id_IDX USING BTREE ON test_opened_ticket (test_town_id,test_country_id);
CREATE INDEX test_ticket_id_IDX USING BTREE ON test_ticket (id);
CREATE INDEX test_town_id_IDX USING BTREE ON test_town (id);
CREATE INDEX test_country_id_IDX USING BTREE ON test_country (id);

#slower
with my_opened_tickets as(
select
    test_town.id  as id,
    test_opened_ticket.nb2,
    test_opened_ticket.nb1
from
    test_town ,
    test_ticket ,
    test_opened_ticket
where
    test_opened_ticket.id = test_ticket.id
    and test_opened_ticket.test_country_id = 186
    and test_opened_ticket.test_town_id = test_town.id
    ),
max_nb2 as(
select
    id,
    max(nb2) nb2
from
    my_opened_tickets
group by
    id ),
     max_by_id_nb2 as (
select
    max_nb2.id,
    max_nb2.nb2,
    max(my_opened_tickets.nb1)
from
    my_opened_tickets ,
    max_nb2
where
    my_opened_tickets.id = max_nb2.id
    and max_nb2.nb2 = my_opened_tickets.nb2
group by
    max_nb2.id,max_nb2.nb2)
    select * from max_by_id_nb2;



#faster
with my_opened_tickets as(
select
    test_opened_ticket.test_town_id  as id,
    test_opened_ticket.nb2,
    test_opened_ticket.nb1
from
    test_town ,
    test_ticket ,
    test_opened_ticket
where
    test_opened_ticket.id = test_ticket.id
    and test_opened_ticket.test_country_id = 186
    and test_opened_ticket.test_town_id = test_town.id
    ),
max_nb2 as(
select
    id,
    max(nb2) nb2
from
    my_opened_tickets
group by
    id ),
     max_by_id_nb2 as (
select
    max_nb2.id,
    max_nb2.nb2,
    max(my_opened_tickets.nb1)
from
    my_opened_tickets ,
    max_nb2
where
    my_opened_tickets.id = max_nb2.id
    and max_nb2.nb2 = my_opened_tickets.nb2
group by
    max_nb2.id,max_nb2.nb2)
    select * from max_by_id_nb2;