Mysql – I need help optimizing a not that complicated query that returns unexpedcted performance problems

indexmariadbMySQL

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:
Execution plan

Thank you.

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.
https://www.mediafire.com/?ph0gk06dxntohmg

Best Answer

My bet is on creating the following indexes:

(incoming, targetNumber, dateCreated)
(sourceNumber, incoming, dateCreated)

But you have to rewrite your query to do:

select *
from tesis.call oc --outgoing calls
join tesis.call ic on 
    oc.sourceNumber = ic.targetNumber 
    and ic.dateCreated > oc.dateCreated 
    and (ic.dateCreated - interval 30 second) <= oc.dateCreated --incoming calls
where oc.incoming = 0 
    and ic.incoming = 1 
    and oc.neighborhood_id is not null

This seems better than doing the JOIN in this direction oc -> ic, where the indexes would be: (incoming) (targetNumber, dateCreated)

Please note that this is long shot, as I do not know the cardinality of the conditions, I can only imagine them. Please test it and show the results of EXPLAIN to check it.