| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | auto_increment |
| account_id | int(11) | YES | MUL | NULL | |
| incoming_rest | double | YES | NULL | ||
| outgoing_rest | double | YES | NULL | ||
| discount | double | YES | NULL | ||
| discount_without_tax | double | YES | NULL | ||
| discount_with_tax | double | YES | NULL | ||
| service_id | int(11) | YES | NULL | ||
| service_type | int(11) | YES | NULL | ||
| discount_period_id | int(11) | YES | NULL | ||
| slink_id | int(11) | YES | NULL | ||
| discount_date | int(11) | YES | MUL | NULL | |
| comment | varchar(255) | YES | NULL | ||
| dealer_transaction_id | int(11) | YES | NULL | ||
| dealer_payment_transaction_id | int(11) | YES | NULL | ||
| is_canceled | int(11) | YES | 0 | ||
| cancel_id | int(11) | YES | 0 |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
|---|---|---|---|---|---|---|---|---|---|---|---|
| discount_transactions_all | 0 | PRIMARY | 1 | id | A | 6353629 | NULL | NULL | BTREE | ||
| discount_transactions_all | 1 | first_disc | 1 | discount_date | A | 6353629 | NULL | NULL | YES | BTREE | |
| discount_transactions_all | 1 | first_disc1 | 1 | account_id | A | 3212 | NULL | NULL | YES | BTREE |
Полезный апдейт:
UPDATE discount_transactions_all SET discount_date=(100*floor(discount_date/100)) WHERE discount_date NOT REGEXP '00$';
«Округляет» дату списания до 100 секунд. Позволяет уменьшить индекс. По рекомендациям Magnum72. Можно довести до состояния триггера на каждый инсерт в таблицу, но смысла особого пока не вижу, гораздо проще дергать по крону. Аналогичен апдейт для discount_transactions_iptraffic_all.