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
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;
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


