0

I have MySQL tables which store crypto trading activities, one table for each pair. However, those tables are getting heavier and heavier, and requests are taking forever.

Here is the format of one of them :

    CREATE TABLE `aggtrade_bina_adabnb` (
      `exchange_code` varchar(16) NOT NULL,
      `symbol` varchar(16) NOT NULL,
      `aggtrade_id` bigint(20) NOT NULL,
      `price` decimal(16,8) NOT NULL,
      `quantity` decimal(16,8) NOT NULL,
      `first_breakdown_trade_id` bigint(20) NOT NULL,
      `last_breakdown_trade_id` bigint(20) NOT NULL,
      `trade_time` bigint(20) NOT NULL,
      `is_buyer_maker` tinyint(1) NOT NULL,
      `live_data` tinyint(1) DEFAULT 1
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    PARTITION BY HASH (`aggtrade_id`)
    PARTITIONS 4;

Of course, I'm aware my current partitioning is absolutely awful. However, I was wondering if there was a way to partition this table depending automatically on the trade_time field so that I would have one partition for each month, without having to specify those months at table creation's time.

Thanks in advance for your help.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
MD-FRA-83
  • 13
  • 3
  • 2
    There are no Index on your Table. Use Index to speedup your query – Bernd Buffen Jan 25 '20 at 11:20
  • The partitions by trade_time will be helpful only for queries with trade_time parameter in WHERE clause. So you need to know what most frequently query for this table before decide about partitioning. – Slava Rozhnev Jan 25 '20 at 11:24
  • Bernd: Yes, there is an index on trade_time, but I removed it from the example. Slava: All my queries rely on trade_time... – MD-FRA-83 Jan 25 '20 at 11:54
  • 1
    Please read this, especially the section on query performance. https://meta.stackoverflow.com/a/271056/. Then please ask another question to give more information so we can help you better. Often good indexes help much more than partitions. – O. Jones Jan 25 '20 at 14:13

0 Answers0