3
IN MySQL 8 (>= 8.0.16) You can use a CHECK CONSTRAINT
In older Versions you will need an INSERT/UPDATE TRIGGER
That said, you should also normalize your table and use a lookup (or reference/helper) table for an Origin id and a Type id.
CREATE TABLE Product
(
P INT
, PType varchar(19)
, PName varchar(20)
, Price DECIMAL(8,2)
, Origin ...
2
Using intersect almost always means that you should add a composite index.
where `product`.`available` > 0
and `product`.`is_bought_copy` is null
and `product`.`deleted_at` is null
needs
INDEX(is_bought_copy, deleted_at, -- in either order, first since "="
available) -- last, since "range"
That index ...
2
Not much.
Why are there thousands? Hundreds is reasonable.
Each connection takes some amount of RAM; if there are too many connections, you could run out of RAM, which would be bad for performance.
Also, if more than a few dozen connections suddenly get busy, they would be contending with each other for CPU, RAM, I/O, etc.
There is a "wait_timeout"...
2
Possible trick.
Create intermediate table. Copy your ID from t1 into it. Add virtual generated column which calculates some hash from id value, and index it. Use this table as a source for insertion, add sorting by created index expression, and force it (without index hint it may be ignored due to 100% rows selection.. from the other side, it must be used ...
1
Not sure what the rules are re 1 user owning the same device multiple times, if this should increase the count remove the distinct from within the count aggregate function
create table #x (Username varchar(20),DeviceName int)
insert #x (username, devicename) values
('user1',1),
('user1',2),
('user2',3),
('user3',4),
('user4',5),
('user4',6)
select username,...
1
Seconds_behind_master doesn't exactly measure the replication lag. It measures the difference between the timestamp of the last binlog event fetched by the IO thread, versus the timestamp of the last binlog event executed by the SQL thread.
This can become misleading for example if the master has more binlogs that the replica hasn't fetched yet. If the ...
1
Task 1 may be solved by
SELECT t1.*
FROM test t1
JOIN test t2 USING (b_id, c_id, d_id)
WHERE (t1.a_id, t2.a_id) IN ((1,2), (2,1))
1
... I was told to create a stored procedure, and call the view inside it.
You don't need a View.
You don't need a Stored Procedure.
Just select the data that you want to display, based on the criteria that you have.
select id, name, status, usertype, flag
from tbl_1
where usertype = ?
order by id ;
The usertype argument will come from [the value of the ...
1
Investigate the plan. MySQL alters the order of the tables scanning.
Add STRAIGHT_JOIN. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=592b1fb77c5c383abb8d93f9d9e13559
1
In MySQL you need to make deferent trigger for updating and Inserting;
DELIMITER $$
CREATE TRIGGER before_ITEM _insert
BEFORE INSERT
ON ITEM FOR EACH ROW
BEGIN
SET NEW.Total = NEW.Quantity * NEW.Price;
END $$
DELIMITER ;
CREATE TRIGGER before_ITEM _UPDATE
BEFORE UPDATE
ON ITEM FOR EACH ROW
BEGIN
SET NEW.Total = NEW.Quantity * NEW.Price;
END $$
...
Only top voted, non community-wiki answers of a minimum length are eligible
