3

I am very new to database design, I spun up an Ubuntu server version 20.04.3 LTS and installed mysql version mysql Ver 8.0.31-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)).

I designed my database like so

enter image description here

addresses

use `testdb`;

DROP TABLE IF EXISTS addresses;

CREATE TABLE testdb.addresses ( id INT NOT NULL AUTO_INCREMENT, hash VARCHAR(40) NOT NULL, street VARCHAR(50) NOT NULL, city VARCHAR(25) NOT NULL, state VARCHAR(20) NOT NULL, zipcode VARCHAR(5) NOT NULL, country VARCHAR(40) NOT NULL, coordinates POINT NOT NULL, PRIMARY KEY (id), UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE, UNIQUE INDEX hash_UNIQUE (hash ASC) VISIBLE, SPATIAL KEY coordinates (coordinates) );

sales

use `testdb`;

DROP TABLE IF EXISTS sales;

CREATE TABLE testdb.sales ( id INT NOT NULL AUTO_INCREMENT, saleId BINARY(16) NOT NULL, address_id INT NOT NULL, title VARCHAR(200) NOT NULL, description LONGTEXT NULL DEFAULT NULL, startDate DATE NOT NULL, endDate DATE NULL, link VARCHAR(2048) NULL DEFAULT NULL, source VARCHAR(200) NOT NULL, PRIMARY KEY (id), UNIQUE INDEX saleId_idx (saleId ASC) VISIBLE, INDEX address_id_idx (address_id ASC) VISIBLE, UNIQUE INDEX sale_date_address_idx (address_id ASC, startDate ASC) VISIBLE, CONSTRAINT address_id FOREIGN KEY (address_id) REFERENCES testdb.addresses (id) ON DELETE NO ACTION ON UPDATE NO ACTION);

images

use `sales`;

DROP TABLE IF EXISTS images;

CREATE TABLE testdb.images ( id INT NOT NULL AUTO_INCREMENT, url VARCHAR(2048) NOT NULL, sale_id INT NOT NULL, PRIMARY KEY (id), INDEX sale_id_idx (sale_id ASC) VISIBLE, CONSTRAINT sale_id FOREIGN KEY (sale_id) REFERENCES testdb.sales (id) ON DELETE NO ACTION ON UPDATE NO ACTION);

This is my first database design ever. I think my indicies are fine but I am not certain; when I run a query like the following

SELECT
    sales.id as id, BIN_TO_UUID(sales.saleId) as saleId, sales.title, sales.description, sales.startDate, sales.endDate, sales.link, sales.source,
    addresses.id as address_id, addresses.hash, addresses.street, addresses.city, addresses.state, addresses.zipcode, addresses.country, ST_X(addresses.coordinates) as latitude, ST_Y(addresses.coordinates) as longitude,
    GROUP_CONCAT(images.url) as images
from 
    sales
INNER JOIN 
    addresses ON sales.address_id = addresses.id
INNER JOIN
    images ON images.sale_id = sales.id
WHERE 
    sales.saleId = UUID_TO_BIN('8b56862c-47dc-49dd-9c82-b42c0e92d623')
GROUP BY
    id
LIMIT 
    1;

here is the execution plan enter image description here

it executes fairly fast on around 5k records; however, when I run mysqltuner on my database I get the following:

...
[!!] Joins performed without indexes: 18
...
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 256.0K, or always use indexes with JOINs)

Why does it say I am performing joins without indices when I am only using indices to perform my joins? I am lost, I have been researching without hope.

For reference here are my mysqld configurations

[mysqld]
user            = mysql
innodb_force_recovery = 0
bind-address            = 0.0.0.0
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
binlog_expire_logs_seconds      = 86400

mysqltuner.pl

skip-name-resolve=1 key_buffer_size=0 innodb_log_file_size=16M

Suggested on https://dba.stackexchange.com/questions/218250/help-with-mysqls-maximum-memory-usage-which-is-dang>

join_buffer_size=256K thread_cache_size=40 #query_cache_limit=0 innodb_lru_scan_depth=100 key_cache_age_threshold=7200 key_cache_division_limit=50 key_cache_block_size=16K open_files_limit=30000 table_open_cache=10000 table_definition_cache=2000

EDIT: Running EXPLAIN on the query returns the following.

NOTE: I am using MySQLWorkbench to run my queries which is probably why it comes in a table format, not sure if I can output it in a different way enter image description here

emhsmath
  • 31
  • 2

0 Answers0