Imam problem oko SELECT nad velikom tabelom.
MySQL je 5.5.24
Tabela:
CREATE TABLE `korisnik_log` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`gtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`korisnik_id` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`ID`, `gtime`)
) ENGINE=InnoDB AUTO_INCREMENT=155700486 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
U tabelu se dnevno upiše 1.5 miliona zapisa.
Pretraga je tipa :
SELECT ID, gtime FROM korisnik_log WHERE gtime BETWEEN '2014-06-04' AND '2014-06-07' AND korisnik_id=34567;
Ovaj upit treba da mi vrati oko 1000 zapisa, a izvršavavanje traje po 10tak sec.
Da li postoji način da se ovo ubza?
Radio sam i particionisanje po vremenu, ali ne pomaže baš:
CREATE TABLE `korisnik_log` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`gtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`korisnik_id` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`ID`, `gtime`)
)
PARTITION BY RANGE (unix_timestamp(gtime))
(PARTITION p0 VALUES LESS THAN (unix_timestamp('2014-01-01')) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (unix_timestamp('2014-02-01')) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (unix_timestamp('2014-03-01')) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (unix_timestamp('2014-04-01')) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (unix_timestamp('2014-05-01')) ENGINE = InnoDB
)
[Ovu poruku je menjao maroon984 dana 03.09.2014. u 11:38 GMT+1]