For primary key and Unique key in a table, index is created automatically. Using indexes appropriately can significantly improve query efficiency for large tables and sorting/grouping operations.
Create indexes to increase the performance of query.
Like tableau’s context filter that reduces the time for implementing the query.
Creating Unique index on combination of fields
Unique index on the combination of columns:
Primary key = Unique and Not null
Note:
Care should be taken not to create indexes for every query to avoid storage issues.
Periodically reviewing and removing unnecessary indexes is a good practice.
Functions:
Marking a function as DETERMINISTIC helps the MySQL optimizer make better decisions when optimizing queries that involve the function. It allows the optimizer to optimize the query based on the assumption that the function’s result will not change during query execution.
Basic problem:
To add function in employee table
User Defined Function:
Advanced problem:
User Defined Function as a part of query transformation:
CREATE FUNCTION `experience`(hDate date) RETURNS int
BEGIN
declare exp integer;
select year(now()) - year(hDate) into exp;
RETURN exp;
END
Advanced problem-solving:
To Create a user-defined function calculate_age which returns the age in years and months (e.g. 30 years 5 months) by accepting the DOB column as a parameter.
CREATE TABLE emp_udf (
Emp_ID INT auto_increment primary key,
Name VARCHAR(50),
DOB DATE
);
INSERT INTO Emp_UDF(Name, DOB)
VALUES
("Piyush", "1990-03-30"),
("Aman", "1992-08-15"),
("Meena", "1998-07-28"),
("Ketan", "2000-11-21"),
("Sanjay", "1995-05-21");
delimiter //
create function Calculate_age(DOB date)
returns varchar(100)
deterministic
begin
return concat(
timestampdiff(year, DOB, now()),"years",
timestampdiff(month, DOB, now())%12, "months"
);
end//
select *, Calculate_age(DOB) as Age
from Emp_UDF;
Drop function calculate_age;
Recent Comments