Mysql – Working with indexes – is this normal behavior

MySQL

I have a roughly 4gb database with most data stored in a single table – it currently has over 34 million entries with 8 datapoints each and two indexes;

CREATE TABLE `cdr_records` (
  `dateTimeOrigination` int(11) DEFAULT NULL,
  `callingPartyNumber` varchar(50) DEFAULT NULL,
  `originalCalledPartyNumber` varchar(50) DEFAULT NULL,
  `finalCalledPartyNumber` varchar(50) DEFAULT NULL,
  `pkid` varchar(50) NOT NULL DEFAULT '',
  `duration` int(11) DEFAULT NULL,
  `origDeviceName` varchar(50) DEFAULT NULL,
  `destDeviceName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`pkid`),
  KEY `tableIndex` (`dateTimeOrigination`,`callingPartyNumber`,`originalCalledPartyNumber`,`finalCalledPartyNumber`),
  KEY `webPageIndex1` (`callingPartyNumber`,`originalCalledPartyNumber`,`finalCalledPartyNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A query can take one of two forms;

1) Show me all entries for a value. This value can be in 'callingPartyNumber', originalCalledPartNumber', or 'finalCalledPartyNumber'.

select 
    from_unixtime(dateTimeOrigination) as date_of_call_origination,
    callingPartyNumber as calling_party_number, 
    originalcalledpartynumber as original_called_party_number,
    finalCalledPartyNumber as final_called_party_number, 
    SEC_TO_TIME(duration) as duration, 
    origDeviceName, 
    destDeviceName from cdr_records
  where 
    (callingPartyNumber=? or originalcalledpartynumber=? or finalcalledpartynumber=?)
  order by datetimeorigination desc;

2) Show me all entries for a value between two given dates in 'dateTimeOrigination'. This value can be in 'callingPartyNumber', originalCalledPartNumber', or 'finalCalledPartyNumber'. For example;

select 
    from_unixtime(dateTimeOrigination) as date_of_call_origination,
    callingPartyNumber as calling_party_number, 
    originalcalledpartynumber as original_called_party_number,
    finalCalledPartyNumber as final_called_party_number, 
    SEC_TO_TIME(duration) as duration, 
    origDeviceName, 
    destDeviceName from cdr_records
  where 
    (callingPartyNumber=? or originalcalledpartynumber=? or finalcalledpartynumber=?)
  and from_unixtime(dateTimeOrigination)
                      between         '2017-01-24'
                          and ADDDATE('2017-01-25', INTERVAL 1 DAY)
  order by datetimeorigination desc;

When I run an explain on either query, they match with the 'webPageIndex1' index. At this point, any query I run takes about 48 seconds.

So, a couple of question;

1) I would have thought that the second type of query would match with the 'tableIndex' index instead of 'webPageIndex1' as it specifies beginning and end dates – am I seeing this wrong (the first query does match with 'webPageIndex1')?

2) 48 seconds seems like a long time for an indexed search.

Any help is appreciated.

Updates to indexes and statement optimization suggestions;

    CREATE TABLE `cdr_records` (
      `dateTimeOrigination` int(11) DEFAULT NULL,
      `callingPartyNumber` varchar(50) DEFAULT NULL,
      `originalCalledPartyNumber` varchar(50) DEFAULT NULL,
      `finalCalledPartyNumber` varchar(50) DEFAULT NULL,
      `pkid` varchar(50) NOT NULL DEFAULT '',
      `duration` int(11) DEFAULT NULL,
      `origDeviceName` varchar(50) DEFAULT NULL,
      `destDeviceName` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`pkid`),
      KEY `tableIndex` (`dateTimeOrigination`,`callingPartyNumber`,`originalCalledPartyNumber`,`finalCalledPartyNumber`),
      KEY `callingPartyNumberIndex` (`callingPartyNumber`,`dateTimeOrigination`),
      KEY `originalCalledPartyNumberIndex` (`originalCalledPartyNumber`,`dateTimeOrigination`),
      KEY `finalCalledPartyNumberIndex` (`finalCalledPartyNumber`,`dateTimeOrigination`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here statement tested;

(
    select from_unixtime(dateTimeOrigination) as date_of_call_origination ,
        callingPartyNumber as calling_party_number,
        originalcalledpartynumber as original_called_party_number,
        finalCalledPartyNumber as final_called_party_number,
        SEC_TO_TIME(duration) as duration,
        origDeviceName, destDeviceName
    from cdr_records
    where callingPartyNumber='?'
)
union distinct (
    select from_unixtime(dateTimeOrigination) as date_of_call_origination ,
        callingPartyNumber as calling_party_number,
        originalcalledpartynumber as original_called_party_number,
        finalCalledPartyNumber as final_called_party_number,
        SEC_TO_TIME(duration) as duration,
        origDeviceName, destDeviceName
    from cdr_records
    where originalcalledpartynumber='?'
)
union distinct (
    select from_unixtime(dateTimeOrigination) as date_of_call_origination ,
        callingPartyNumber as calling_party_number,
        originalcalledpartynumber as original_called_party_number,
        finalCalledPartyNumber as final_called_party_number,
        SEC_TO_TIME(duration) as duration,
        origDeviceName, destDeviceName
    from cdr_records
    where finalcalledpartynumber='?'
)

New update;

explain select from_unixtime(dateTimeOrigination), callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, SEC_TO_TIME(duration), origDeviceName, destDeviceName from cdr_records where (callingPartyNumber='?' or originalCalledPartyNumber='?' or finalCalledPartyNumber='?') and from_unixtime(dateTimeOrigination) between '2017-02-01' and ADDDATE('2017-02-01', INTERVAL 1 DAY) order by dateTimeOrigination desc;
+----+-------------+-------------+-------------+------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------+------+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table       | type        | possible_keys                                                                      | key                                                                                | key_len  | ref  | rows    | Extra                                                                                                                             |
+----+-------------+-------------+-------------+------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------+------+---------+-----------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | cdr_records | index_merge | callingPartyNumberIndex,originalCalledPartyNumberIndex,finalCalledPartyNumberIndex | callingPartyNumberIndex,originalCalledPartyNumberIndex,finalCalledPartyNumberIndex | 53,53,53 | NULL | 2212197 | Using sort_union(callingPartyNumberIndex,originalCalledPartyNumberIndex,finalCalledPartyNumberIndex); Using where; Using filesort |
+----+-------------+-------------+-------------+------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+----------+------+---------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

Update

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.04 sec)

Update
mysql> show session status like 'Handler%';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 12196 |
| Handler_read_last          | 0     |
| Handler_read_next          | 15251 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 12193 |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
16 rows in set (0.00 sec)

Best Answer

OR can often be optimized by using UNION:

( SELECT ...
    FROM ...
    WHERE callingPartyNumber = ? )
UNION DISTINCT
( SELECT ...
    FROM ...
    WHERE originalcalledpartynumber = ? )
UNION DISTINCT
( SELECT ...
    FROM ...
    WHERE finalcalledpartynumber = ? )

And have 3 separate indexes, one per SELECT:

INDEX(callingPartyNumber,       dateTimeOrigination)
INDEX(originalcalledpartynumber,dateTimeOrigination)
INDEX(finalcalledpartynumber,   dateTimeOrigination)

Your composite indexes are useful only if you have all those columns in the WHERE and they are connected with AND, not `OR.

Those 3 indexes will work equally well for both of your queries (after the further hint, below).

Don't hide columns in function calls:

from_unixtime(dateTimeOrigination) between ...

Instead

dateTimeOrigination BETWEEN unix_timestamp(...) AND unix_timestamp(...)

More debugging

Run this:

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

This will help us diagnose whether the query is hitting more rows than "it ought to".

UNION vs Index merge

One of the EXPLAINs shows "Index merge". This is rarely used, but when it is used for OR, it will (probably) be better than using the UNION that I am recommended. So... Do the EXPLAIN, if it does not say "Index merge" (and multiple numbers in Key_len), then use UNION.

Related Question