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`)

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

enter image description here

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

  • 1
    pigeon_id is varchar(200)? Why? Oct 30 '17 at 11:10
  • 2
    Your query would benefit from an index on (pigeon_id, pigeon_station, date_time). Replace your index1 with the above index. Oct 30 '17 at 11:12
  • 1
    And if you don't need the "signal_strength" in the output, the simpler query woul do: select MIN(date_time) as date_time, pigeon_id, pigeon_station from pigeon_signal group by pigeon_id, pigeon_station ; Oct 30 '17 at 11:16
  • 1
    On top, make a database. Tables for Pidgeon and Station, integer ID's referencing the central signal table. You waste space with the varchars, which means more IO and a lot more processing for string comparisons.
    – TomTom
    Oct 30 '17 at 11:39
  • @ypercubeᵀᴹ Thanks. I have updated the question. Oct 30 '17 at 14:03

Presumably a pigeon cannot arrive at a station twice in one second? Then add this:

PRIMARY KEY(pigeon_id, pigeon_station, date_time)

This will improve on ypercube's suggestion, perhaps by a factor of 2.

At that point, probably both of your indexes become useless. But before that, let's see the other important queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.