mysqltuner says my joins are not using index but I have index
3
votes
0
answers
617
views
I am very new to database design, I spun up an Ubuntu server version
**addresses**
it executes fairly fast on around 5k records; however, when I run
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

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

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
.cnf
[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**

Asked by emhsmath
(31 rep)
Dec 2, 2022, 07:06 AM
Last activity: Dec 3, 2022, 04:51 AM
Last activity: Dec 3, 2022, 04:51 AM