CODEWITHSUNDEEP
mysql
Saint Robson
Saint Robson

Reputation: 5525

INDEX is not improving sql query result

I have this SQL command :

SELECT t1.timestamp, t1.fee, t1.fee_unit, t1.profit, t1.profit_unit, t1.profit_fee_pair, price_trading.bid_price, price_trading.ask_price
FROM (
    SELECT trading_fredy.timestamp, SUM(trading_fredy_fee.fee) AS fee, trading_fredy_fee.fee_unit, trading_fredy.profit, trading_fredy.profit_unit,
        IF (trading_fredy.profit_unit = 'BTC' OR
        trading_fredy.profit_unit = 'ETH' OR
        trading_fredy.profit_unit = 'USDT', 
        CONCAT(trading_fredy_fee.fee_unit, trading_fredy.profit_unit),
        CONCAT(trading_fredy.profit_unit, trading_fredy_fee.fee_unit)) AS profit_fee_pair
    FROM trading_fredy
    LEFT JOIN trading_fredy_fee
    ON trading_fredy.order_id = trading_fredy_fee.order_id
    WHERE trading_fredy_fee.fee != 0
    AND trading_fredy.status = 'CLOSED'
    GROUP BY trading_fredy.timestamp
    ORDER BY trading_fredy.date ASC    
) AS t1
LEFT JOIN price_trading
ON price_trading.timestamp = t1.timestamp
AND price_trading.pair_name = t1.profit_fee_pair
LEFT JOIN zipped_fee
ON zipped_fee.timestamp = t1.timestamp
WHERE zipped_fee.timestamp IS NULL
AND t1.profit_fee_pair != 'BNBBNB'

and it takes more than 4 seconds to see the result. even only 3 rows.

+---------------------+------------+----------+------------+-------------+-----------------+-------------+-------------+-----------+
| timestamp           | fee        | fee_unit | profit     | profit_unit | profit_fee_pair | bid_price   | ask_price   | timestamp |
+---------------------+------------+----------+------------+-------------+-----------------+-------------+-------------+-----------+
| ml75-1528598537206  | 0.00285750 | BNB      | 0.00024136 | ETH         | BNBETH          |  0.02703500 |  0.02704800 | NULL      |
| lm112-1528598537226 | 0.00570927 | BNB      | 0.00024136 | ETH         | BNBETH          |  0.02703500 |  0.02704800 | NULL      |
| lm129-1528599634045 | 0.00900000 | BNB      | 0.42718954 | USDT        | BNBUSDT         | 15.57000000 | 15.60640000 | NULL      |
+---------------------+------------+----------+------------+-------------+-----------------+-------------+-------------+-----------+
3 rows in set (4.35 sec)

I tried to add INDEX :

ALTER TABLE zipped_fee ADD INDEX addon_fee_buster (timestamp);
ALTER TABLE trading_fredy_fee ADD INDEX addon_fee_buster (fee_unit);

but still need more than 4 seconds to complete. usually, I created INDEX column based on what I need in WHERE clase, but this time I notice that I'm using t1.profit_fee_pair in WHERE clause. which this column is produced by IF, not real column.

update : LEFT JOIN zipped_fee cause the performance issue. before this line, everything was fine.

how to improve this result? thank you.

update : EXPLAIN result

+------+-------------+-------------------+-------+-------------------------------+-------------------------------+---------+-----------------------------------------+------+---------------------------------------------------------------------+
| id   | select_type | table             | type  | possible_keys                 | key                           | key_len | ref                                     | rows | Extra                                                               |
+------+-------------+-------------------+-------+-------------------------------+-------------------------------+---------+-----------------------------------------+------+---------------------------------------------------------------------+
|    1 | PRIMARY     | <derived2>        | ALL   | NULL                          | NULL                          | NULL    | NULL                                    | 8344 | Using where                                                         |
|    1 | PRIMARY     | price_trading     | ref   | price_trading_timestamp_index | price_trading_timestamp_index | 99      | t1.timestamp                            |    9 | Using where                                                         |
|    1 | PRIMARY     | zipped_fee        | index | NULL                          | fee_buster_timestamp          | 35      | NULL                                    | 4124 | Using where; Using index; Using join buffer (flat, BNL join)        |
|    2 | DERIVED     | trading_fredy     | ref   | stormbreaker_status           | stormbreaker_status           | 2       | const                                   | 8344 | Using index condition; Using where; Using temporary; Using filesort |
|    2 | DERIVED     | trading_fredy_fee | ref   | index_trading_fredy_fee       | index_trading_fredy_fee       | 98      | robokoin_binance.trading_fredy.order_id |    1 | Using where                                                         |
+------+-------------+-------------------+-------+-------------------------------+-------------------------------+---------+-----------------------------------------+------+---------------------------------------------------------------------+

update : SHOW CREATE TABLE

| trading_fredy | CREATE TABLE `trading_fredy` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `timestamp` varchar(32) NOT NULL,
  `pair` varchar(10) NOT NULL DEFAULT '',
  `action` enum('BUY','SELL') DEFAULT NULL,
  `volume` decimal(20,8) DEFAULT NULL,
  `price` decimal(20,8) DEFAULT NULL,
  `price_cutloss` decimal(20,8) DEFAULT NULL,
  `profit` decimal(20,8) DEFAULT NULL,
  `profit_unit` varchar(4) DEFAULT NULL,
  `order_id` varchar(32) NOT NULL,
  `status` enum('OPEN','CLOSED','PENDING','LOST','NO SUBMIT','RESUBMIT','ERROR','CUT LOSS') DEFAULT NULL,
  `fee` decimal(20,8) unsigned NOT NULL,
  `profit_bnb` decimal(20,8) unsigned DEFAULT NULL,
  `robo` varchar(8) DEFAULT NULL,
  `last_checked` datetime DEFAULT NULL,
  `volume_filled` decimal(20,8) unsigned DEFAULT NULL,
  `profit_released` enum('yes','no') DEFAULT 'no',
  PRIMARY KEY (`id`),
  KEY `trading_fredy_index` (`timestamp`,`pair`,`profit_unit`,`status`,`profit_released`),
  KEY `stormbreaker_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=16736 DEFAULT CHARSET=utf8 |

| trading_fredy_fee | CREATE TABLE `trading_fredy_fee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bnb_id` int(20) unsigned NOT NULL,
  `order_id` varchar(32) NOT NULL,
  `price` decimal(20,8) NOT NULL,
  `vol` decimal(20,8) NOT NULL,
  `fee` decimal(20,8) NOT NULL,
  `fee_unit` varchar(5) NOT NULL,
  `paid` enum('yes','no') DEFAULT 'no',
  PRIMARY KEY (`id`),
  KEY `index_trading_fredy_fee` (`order_id`),
  KEY `fee_buster` (`paid`)
) ENGINE=InnoDB AUTO_INCREMENT=28768 DEFAULT CHARSET=utf8 |

| price_trading | CREATE TABLE `price_trading` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `client_name` varchar(32) DEFAULT NULL,
  `timestamp` varchar(32) DEFAULT NULL,
  `pair_name` varchar(8) DEFAULT NULL,
  `bid_price` decimal(20,8) DEFAULT NULL,
  `ask_price` decimal(20,8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `price_trading_index` (`client_name`,`timestamp`,`pair_name`),
  KEY `price_trading_timestamp_index` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=284941 DEFAULT CHARSET=utf8 |

| zipped_fee | CREATE TABLE `zipped_fee` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `client_name` varchar(32) DEFAULT NULL,
  `timestamp` varchar(32) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `pair` varchar(10) DEFAULT NULL,
  `action` enum('BUY','SELL') DEFAULT NULL,
  `volume` decimal(20,8) DEFAULT NULL,
  `price` decimal(20,8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fee_buster_timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=3991 DEFAULT CHARSET=latin1 |

Upvotes: 3

Views: 74

Answers (1)

Saint Robson
Saint Robson

Reputation: 5525

I found the problem.

this because zipped_fee has CHARSET=latin1 while other tables CHARSET=utf8.

once I run this command :

ALTER TABLE zipped_fee CONVERT TO CHARACTER SET utf8;

everything works.

Upvotes: 2

Related Questions