Index relations with other tables

Question

I have a table like this :

mysql> SHOW CREATE TABLE lz_stats_aggs\G
*************************** 1. row ***************************
       Table: lz_stats_aggs
Create Table: CREATE TABLE `lz_stats_aggs` (
  `year` smallint(4) unsigned NOT NULL DEFAULT '0',
  `month` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `day` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `time` int(10) unsigned NOT NULL DEFAULT '0',
  `mtime` int(10) unsigned NOT NULL DEFAULT '0',
  `sessions` int(10) unsigned NOT NULL DEFAULT '0',
  `visitors_unique` int(10) unsigned NOT NULL DEFAULT '0',
  `conversions` int(10) unsigned NOT NULL DEFAULT '0',
  `aggregated` int(10) unsigned NOT NULL DEFAULT '0',
  `chats_forwards` int(10) unsigned NOT NULL DEFAULT '0',
  `chats_posts_internal` int(10) unsigned NOT NULL DEFAULT '0',
  `chats_posts_external` int(10) unsigned NOT NULL DEFAULT '0',
  `avg_time_site` double unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`year`,`month`,`day`),
  KEY `time` (`time`),
  KEY `aggregated` (`aggregated`),
  KEY `mtime` (`mtime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)



mysql> describe lz_stats_aggs;
+----------------------+----------------------+------+-----+---------+-------+
| Field                | Type                 | Null | Key | Default | Extra |
+----------------------+----------------------+------+-----+---------+-------+
| year                 | smallint(4) unsigned | NO   | PRI | 0       |       |
| month                | tinyint(3) unsigned  | NO   | PRI | 0       |       |
| day                  | tinyint(3) unsigned  | NO   | PRI | 0       |       |
| time                 | int(10) unsigned     | NO   | MUL | 0       |       |
| mtime                | int(10) unsigned     | NO   | MUL | 0       |       |
| sessions             | int(10) unsigned     | NO   |     | 0       |       |
| visitors_unique      | int(10) unsigned     | NO   |     | 0       |       |
| conversions          | int(10) unsigned     | NO   |     | 0       |       |
| aggregated           | int(10) unsigned     | NO   | MUL | 0       |       |
| chats_forwards       | int(10) unsigned     | NO   |     | 0       |       |
| chats_posts_internal | int(10) unsigned     | NO   |     | 0       |       |
| chats_posts_external | int(10) unsigned     | NO   |     | 0       |       |
| avg_time_site        | double unsigned      | NO   |     | 0       |       |
+----------------------+----------------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

I have few other tables with the same fields day, month and year as primary key. I now need to insert data into this table for the dates 17/7/2020 till today. However, when I do that, I get error :

mysql> LOAD DATA INFILE '/var/opt/rh/rh-mysql57/lib/mysql-files/lz_stats_aggs07.csv' INTO TABLE lz_stats_aggs FIELDS TERMINATED BY ',';
ERROR 1062 (23000): Duplicate entry '2020-7-20' for key 'PRIMARY'

When I check the table for the dates after the 17th, I get this :

mysql> select * from lz_stats_aggs where day >= '16' and month = '7' and year = '2020';
+------+-------+-----+------------+----------+----------+-----------------+-------------+------------+----------------+----------------------+----------------------+---------------+
| year | month | day | time       | mtime    | sessions | visitors_unique | conversions | aggregated | chats_forwards | chats_posts_internal | chats_posts_external | avg_time_site |
+------+-------+-----+------------+----------+----------+-----------------+-------------+------------+----------------+----------------------+----------------------+---------------+
| 2020 |     7 |  16 | 1595235228 |  3557100 |      416 |             416 |           0 | 1595235227 |              0 |                  982 |                  987 |     1316.7668 |
| 2020 |     7 |  17 | 1595005244 | 10273000 |      409 |             408 |           0 | 1595005243 |              0 |                  719 |                  614 |     2075.2298 |
| 2020 |     7 |  18 | 1595091680 | 49116400 |        0 |               0 |           0 | 1595091680 |              0 |                    0 |                    0 |             0 |
| 2020 |     7 |  19 | 1595178102 | 97573600 |        1 |               1 |           0 | 1595178102 |              0 |                    0 |                    0 |          7859 |
| 2020 |     7 |  20 | 1595385572 | 99382700 |        0 |               0 |           0 | 1595385572 |              0 |                    0 |                    0 |             0 |
| 2020 |     7 |  24 | 1595670196 | 56234500 |        0 |               0 |           0 | 1595670196 |              0 |                    0 |                    0 |             0 |
| 2020 |     7 |  25 | 1597584414 |  1487900 |        0 |               0 |           0 | 1597584413 |              0 |                    0 |                    0 |             0 |
+------+-------+-----+------------+----------+----------+-----------------+-------------+------------+----------------+----------------------+----------------------+---------------+
7 rows in set (0.00 sec)

My question is if I delete these rows, how will it affect other tables in this database? What is the function of the "KEY" items like time, aggregated and mtime? Will this cause problems in other tables when I delete these rows?

Your table above has a composite primary key consisting of the year, month, and day. This is not an optimal way in general to store a date in MySQL. A better approach would be to use a single date column. As for the error, you already have a 20th July 2020 record in there, hence the failure. You could delete this record. If any other tables have foreign keys pointing to this record, you would get an error message, and would have to clean up those other table(s) first. — Tim Biegeleisen, Aug 17 '20 at 07:31
This question is impossible to answer without knowing the **whole** DB design. If the primary key is used as FK, there can be a constrain violation or the actions performed by [`ON DELETE CASCADE/SET NULL/...`](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html) If you want to know the tables referencing this table, see [How do I see all foreign keys to a table or column?](https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column) — Cid, Aug 17 '20 at 07:33
You are the only one who knows if deleting these records will cause any problems with other tables! We know nothing about the other tables, nor what all these tables mean from the application's point of view. — Shadow, Aug 17 '20 at 07:35
@Cid the table in the question is of myisam type, no fks there. — Shadow, Aug 17 '20 at 07:36

Index relations with other tables

0 Answers0