|
|
Creating indexes on table "Employees" can improve performance of specific
queries:
-- using ALTER TABLE to create PRIMARY KEY index:
ALTER TABLE Employees ADD PRIMARY KEY
(EmpNo);
-- syntax for creating index on one field:
CREATE INDEX indx_emp_managerno
ON Employees (ManagerNo);
/* these two indexes is supposed to hugely improve performance of INNER JOINs
like this one: */
SELECT a.DeptNo, a.EmpNo,
CONCAT(a.FirstName,a.LastName) AS EmpName,
CONCAT(b.FirstName,b.LastName) AS ManagerName
FROM Employees AS a
INNER JOIN Employees AS b ON
a.ManagerNo = b.EmpNo
ORDER BY a.DeptNo;
/* simple syntax for creating index on 3 address fields. it supposed to speed
up address searching */
CREATE INDEX indx_emp_address
ON Employees (City,Street,HouseNum);
-- the same using ALTER TABLE syntax:
ALTER TABLE Employees ADD INDEX
indx_emp_address (City,Street,HouseNum);
-- this index have to accelerate the execution of the
following query:
SELECT EmpNo, FirstName, LastName, City, Street,
HouseNum
FROM Employees
WHERE City = 'berlin' AND Street = 'aufen str';
-- or of the following JOIN:
SELECT a.EmpNo, a.FirstName, a.LastName, a.City, b.x, b.y
FROM Employees AS a INNER JOIN Locations AS b
ON a.City = b.City
ORDER BY a.City;
-- if you do not need this index any more, drop it:
ALTER TABLE Employees DROP INDEX
indx_emp_address;
|
|