BiTel

# Query 4: 8.66 QPS, 0.03x concurrency, ID 0x87B7A14FE7FE57D7 at byte 395266634 # This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2013-11-06 13:34:07 to 2013-11-07 09:05:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         69  608633
# Exec time     12   2230s     3ms   254ms     4ms     4ms   781us     3ms
# Lock time     51     44s    20us   245ms    73us   125us   333us    63us
# Rows sent      3 200.12k       0       2    0.34    0.99    0.47       0
# Rows examine  58   2.00G   3.45k   3.45k   3.45k   3.35k       0   3.35k
# Query size    59 154.47M     261     271  266.12  258.32       0  258.32
# String:
# Databases    bgbilling
# Hosts        bg2.dsi.lo... (608630/99%), localhost (3/0%)
# Users        bill
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `bgbilling` LIKE 'inv_device_port_subscription_30'\G
#    SHOW CREATE TABLE `bgbilling`.`inv_device_port_subscription_30`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT SQL_CALC_FOUND_ROWS * FROM inv_device_port_subscription_30 WHERE deviceId=7 AND (228=-1 OR port=228) AND (dateFrom IS NULL OR NULL IS NULL OR dateFrom<=NULL) AND (dateTo IS NULL OR '2013-11-07 00:00:00' IS NULL OR dateTo>='2013-11-07 00:00:00') ORDER BY port\G

mysql> explain extended SELECT SQL_CALC_FOUND_ROWS * FROM inv_device_port_subscription_30 WHERE deviceId=7 AND (228=-1 OR port=228) AND (dateFrom IS NULL OR NULL IS NULL OR dateFrom<=NULL) AND (dateTo IS NULL OR '2013-11-07 00:00:00' IS NULL OR dateTo>='2013-11-07 00:00:00') ORDER BY port
    -> ;
+----+-------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table                           | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | inv_device_port_subscription_30 | ALL  | NULL          | NULL | NULL    | NULL | 3752 |   100.00 | Using where |
+----+-------------+---------------------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show create table inv_device_port_subscription_30;
+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                           | Create Table                                                                                                                                                                                                                                                                                                                                                                     |
+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inv_device_port_subscription_30 | CREATE TABLE `inv_device_port_subscription_30` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceId` int(11) NOT NULL,
  `port` int(11) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `subscriberId` int(11) NOT NULL,
  `subscriberTitle` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5195 DEFAULT CHARSET=utf8 |
+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain extended SELECT SQL_CALC_FOUND_ROWS * FROM inv_device_port_subscription_30 WHERE deviceId=7 AND (228=-1 OR port=228) AND (dateFrom IS NULL OR NULL IS NULL OR dateFrom<=NULL) AND (dateTo IS NULL OR '2013-11-07 00:00:00' IS NULL OR dateTo>='2013-11-07 00:00:00') ORDER BY port
    -> ;
+----+-------------+---------------------------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
| id | select_type | table                           | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------------------------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | inv_device_port_subscription_30 | ref  | device_port   | device_port | 8       | const,const |    1 |   100.00 | Using where |
+----+-------------+---------------------------------+------+---------------+-------------+---------+-------------+------+----------+-------------