Mysql Index in PHP

In this article I explain how to create and drop mysql indexes in PHP.
  • 2799

Introduction

An "index" can improves the speed of operation in a table. MySQL automatically creates an index for primary key, foreign key, and unique constraints. In addition, you may want to create "indexes" for other columns that are frequently used in joins or search condition. You can use DROP INDEX statement  to drop an index.

Syntax

CREATE [UNIQUE] INDEX index_name
ON [database_name.] [table_name] (column_name1[ASC|DESC] , column_name2[ASC|DESC]............................)

Example of MySQL create and drop MySQL index in PHP

<?php

$con=mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("mysql", $con);

print "<h2>MySQL: Simple select statement</h2>";

$result = mysql_query("select * from emp_dtl");

echo "<table border='1'>

<tr>

<th>EmpId</th>

<th>Firstname</th>

<th>Lastname</th>

<th>Role</th>

<th>Salary</th>

</tr>";

while($row = mysql_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['id'] . "</td>";

echo "<td>" . $row['Firstname'] . "</td>";

echo "<td>" . $row['Lastname'] . "</td>";

echo "<td>" . $row['role'] . "</td>";

echo "<td>" . $row['salary'] . "</td>";

echo "</tr>";

}

echo "</table>";

 

//Create an index in PHP

$result = mysql_query("CREATE INDEX xyz ON emp_dtl(id)",$con);

print "<h2>MySQL: MySQl index has been Created. </h2>";

 

//Drop an index in PHP

$result = mysql_query("DROP INDEX xyz ON emp_dtl",$con);

print "<h2>MySQL: MySQl index has been Removed. </h2>";

mysql_close($con);

?>

Output

mysql-index-in-php.jpg

© 2020 DotNetHeaven. All rights reserved.