Quesiton on MySQL/MariaDB KEY AND SELECT order

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

Quesiton on MySQL/MariaDB KEY AND SELECT order

Postby vicn1222 » 23. November 2021 22:24

Hi,

Can someone help me on below questions regarding MySQL/MariaDB? Many thanks.

1. In below two tables, I have UNIQUE KEY ( reporter_id, report_period ), I also need lookups based on reporter_id and report_period. Do I need to define KEY ( reporter_id ), and KEY ( report_period )?

2. When doing select, is there any difference between "WHERE report_table.id = report_summary.id" AND "WHERE report_summary.id = report_table.id". That is, is there any difference in #3 and #4 selects?

Code: Select all
1. Create report_table:
CREATE TABLE report_table
(
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  report_period date NOT NULL,
  reporter_id int(11) unsigned NOT NULL,
  dir varchar(20) NOT NULL,
  report_content mediumblob NOT NULL,
  PRIMARY KEY ( id ),
  UNIQUE KEY ( reporter_id, report_period )
  KEY ( reporter_id ),
  KEY ( report_period )
);

2. Create report_summary
CREATE TABLE report_summary
(
  id int(11) unsigned NOT NULL,
  buy_amount double DEFAULT NULL,
  sell_amount double DEFAULT NULL,
  PRIMARY KEY ( id ),
  CONSTRAINT FOREIGN KEY ( id ) REFERENCES report_table ( id )
  ON DELETE CASCADE ON UPDATE CASCADE
);

3. Select
SELECT report_table.*, buy_amount, sell_amount
FROM report_table, report_summary
WHERE report_summary.id = report_table.id
AND reporter_id = 12
AND report_period >= '2000-01-01'
AND report_period <= '2000-12-31';

4. Select
SELECT report_table.*, buy_amount, sell_amount
FROM report_table, report_summary
WHERE report_table.id = report_summary.id
AND reporter_id = 12
AND report_period >= '2000-01-01'
AND report_period <= '2000-12-31';
vicn1222
 
Posts: 1
Joined: 23. November 2021 22:22
XAMPP version: What is it?
Operating System: CentOS 8

Re: Quesiton on MySQL/MariaDB KEY AND SELECT order

Postby Nobbie » 25. November 2021 10:42

1) yes
2) no
Nobbie
 
Posts: 13171
Joined: 09. March 2008 13:04


Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 17 guests