I have a table of pigeon data.
pigeon_id = ID of pigeon
date_time = time of signal_event
pigeon_station = id of pigeon station
signal_strength = signal strength of pigeon
CREATE TABLE `pigeon_signal` ( `pigeon_id` varchar(20) NOT NULL, `date_time` int(11) DEFAULT NULL, `signal_strength` decimal(10,0) NOT NULL, `pigeon_station` varchar(20) DEFAULT NULL, KEY `index1` (`pigeon_id`,`pigeon_station`), KEY `index2` (`pigeon_id`,`date_time`,`pigeon_station`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Table count: 500,000 rows
Each pigeon has a transmitter. Every few seconds the pigeon station records the signal strength of all the surrounding pigeons.
I want to query the first time a pigeon station sees a pigeon. Here is my sql. It is slow but it returns the correct results:
select ps2.* from pigeon_signal as ps join (select MIN(date_time) as date_time, pigeon_id, pigeon_station from pigeon_signal group by pigeon_id, pigeon_station) ps2 on ps.date_time = ps2.date_time and ps.pigeon_id = ps2.pigeon_id and ps.pigeon_station = ps2.pigeon_station
The query takes about 2 seconds to run (too long).
Is there a better way of querying this data?
I would appreciate any tips/hints/advice related to this problem.
Many thanks :D
Thanks for the answers!! :D
I have changed the pigeon_id column to a varchar(20).
@ypercubeᵀᴹ I changed the index to what you suggested and it is the fastest (thanks!!!) But why?
distinct pigeon_id: 25306
distinct pigeon_station: 36
distinct date_time: 452322
My thinking: Order the index attributes by the number of distinct elems ascending. Results:
1. pigeon_station,pigeon_id, date_time: 1.8 seconds
2. pigeon_id, pigeon_station, date_time: 0.4 seconds
3. pigeon_id,date_time,pigeon_station: 1.8 seconds
4. date_time, pigeon_id, pigeon_station: 1.9 seconds
Why is option 2 faster than option 1? As there are less distinct pigeon_station values than pigeon_id.
But if the optimal index should order the attributes by the number of distinct values descending then why isn't option 4 the fastest?
Many thanks for your time