Sample Header Ad - 728x90

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 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
.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** enter image description here
Asked by emhsmath (31 rep)
Dec 2, 2022, 07:06 AM
Last activity: Dec 3, 2022, 04:51 AM