I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the receiver.
Before you tell me to separate the data in two tables, one for incoming calls and another for outgoing calls, I tried to do it but my thesis partners didn't agree so I had to go along with a single table, differentiating them with an "incoming" int column.
We are trying to match each outgoing call to an incoming call. The criteria for that is that the incoming call will have been created within 30 seconds of the outgoing call, and that their numbers match.
select * from tesis.call oc --outgoing calls join tesis.call ic --incoming calls on oc.sourceNumber = ic.targetNumber and ic.dateCreated > oc.dateCreated and ic.dateCreated <= (oc.dateCreated + interval 30 second) where oc.incoming = 0 and ic.incoming = 1 and oc.neighborhood_id is not null
We have an index for "incoming", an index consisting of (sourceNumber, targetNumber), and for in dateCreated.
tesis.call has around 13000 rows
sourceNumber and targetNumber are varchar(20)
dateCreated is DateTime
incoming is int
For some reason, the query takes around 100 seconds to execute, tested in two computers with the DB's on localhost, running with an i5 processor, one of them on MySQL and the other on MariaDB.
I tried many index combinations and none of them work.
This is the execution plan with the provided indexes:
Right now the provided query with the specified indexes took: 0,780 sec. (+ 116,455 sec. network) to execute.
The network part looks very weird to me, since "select * from tesis.call" takes 0,000 sec. (+ 0,062 sec. network) to execute so it doesn't look like a connectivity issue.
EDIT: Here is the result of SHOW CREATE TABLE tesis.call:
CREATE TABLE `call` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `dateCreated` datetime(6) DEFAULT NULL, `incoming` int(11) DEFAULT NULL, `operatorName` varchar(20) DEFAULT NULL, `sourceNumber` varchar(20) DEFAULT NULL, `targetNumber` varchar(20) DEFAULT NULL, `batteryLevel` double DEFAULT NULL, `currentSignal` double DEFAULT NULL, `locationLat` double DEFAULT NULL, `locationLon` double DEFAULT NULL, `dispatchDate` datetime DEFAULT NULL, `insertionDate` datetime DEFAULT NULL, `matchId` int(10) unsigned DEFAULT NULL, `callTime` int(10) DEFAULT '0', `neighborhood_id` int(11) DEFAULT NULL, `Geom` point DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`), UNIQUE KEY `matchId_UNIQUE` (`matchId`), KEY `Geom` (`Geom`(25)), KEY `incoming` (`incoming`) USING HASH, KEY `dateCreated` (`dateCreated`), KEY `sourceNumber_targetNumber` (`sourceNumber`,`targetNumber`), KEY `dateCreated_incoming_targetNumber` (`incoming`,`targetNumber`,`dateCreated`), KEY `dateCreated_incoming_sourceNumber` (`incoming`,`sourceNumber`,`dateCreated`) ) ENGINE=InnoDB AUTO_INCREMENT=15154 DEFAULT CHARSET=utf8
EDIT 2: I uploaded a script to recreate the table in case anyone wants to do some tests.