Why I get "Copying to tmp table on disk" after several iteration of loop? In the first few iterations I have not this. Table has 10 million and more rows. When I set lover offset, loop execute more iteration without "tmp table".
My select is
SELECT u.domena_id, u.umiestnenie, u.datum
FROM (SELECT domena_id, min(datum) as min_datum
FROM umiestnenie
WHERE datum BETWEEN 'date1' AND 'date2'
GROUP BY domena_id
LIMIT 200000
OFFSET offset (increasing in lopp)
) x
INNER JOIN umiestnenie u ON u.domena_id = x.domena_id and u.datum = x.min_datum
Table umiestnenie
CREATE TABLE IF NOT EXISTS `umiestnenie` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`domena_id` int(11) NOT NULL,
`datum` date NOT NULL,
`umiestnenie` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_umiestnenie_domeny_idx` (`domena_id`),
KEY `datum_idx` (`datum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=15243077 ;
Table domena
CREATE TABLE IF NOT EXISTS `domena` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nazov` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `nazov` (`nazov`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=15343156 ;
Why when I run this select one time, it doesn't need temporary table and when I run this select more times it does?
This is Explain of my select
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1000000
1 PRIMARY u ref domena_id_idx domena_id_idx 4 x.domena_id 7 Using where
2 DERIVED umiestnenie index NULL domena_id_idx 4 NULL 20109031 Using where
Here is not tmp table, but when I run this select with increased offset from php I see using of temp table.
