Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Indexes in MySQL

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 259
    Comment on it

    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
    FROM EmplyeTable
    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; 

 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: