PHP/MySQL ->
Create index on MySQL table

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;




sqlexamples.info