over 8 years ago
Indexes in MySQL can improve performance of specific queries Like as below Examples :
Syntax for creating index on one field:
CREATE INDEX indx_magrno ON EmplyeTable (EmplyeID);
These two indexes is supposed to hugely improve performance of INNER JOINs like this one:
SELECT a.DeptNo, a.EmpNo, CONCAT(a.Name,a.Addrs) AS EmpName, CONCAT(b.Name,b.Addrs) AS ManagerDetails
FROM EmplyeTable AS a INNER JOIN EmplyeTable AS b ON a.EmplyeID = 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 EmplyeTable (City,Street,HouseNum);
The same using ALTER TABLE syntax:
ALTER TABLE EmplyeTable ADD INDEX indx_emp_address (City,Street,HouseNum);
This index have to accelerate the execution of the following query:
SELECT EmpNo, Name, Addrs, City, Street, HouseNum
WHERE City = 'Test' AND Street = 'Find Nerd';
or of the following JOIN:
SELECT a.EmpNo, a.Name, a.Addrs, a.City, b.x, b.y
FROM EmplyeTable 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 EmplyeTable DROP INDEX indx_emp_address;
Starting with Chrome version 45, NPAPI is no longer supported for Google Chrome. For more information, see Chrome and NPAPI (blog.chromium.org).
Firefox and Microsoft Internet Explorer are recommended browsers for websites using java applets.
Chrome Version Support
Are you sure, you want to delete this comment?
Terms of Service
| © copyright 2022 FindNerd.com. All rights reserved.
Sign up using