CODEWITHSUNDEEP
phpmysql
Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

how to increase performance of mysql query?

I'm using this query to check the username is already exist or not in database using ajax so till now i hv only few records in DB bt in future username list gonna be huge so i want to improve the performance of query in such a way it will take less time to check username existence.

how can i improve following query ? is indexing on db is feasible solution for this ?

check.php

<?php
if(isset($_POST['user_name']))
{
$user_name=$_POST['user_name'];
include("include/conn.php");
$sql_check = mysql_query("select userid from `vector`.`signup` where userid='".$user_name."'")
 or die(mysql_error());

if (mysql_num_rows($sql_check)>0)
{   
    echo "no";
} 
else
{
 echo "yes";
}
}
?> 

my jquery code 

<script language="javascript">

$(document).ready(function()
{
    $("#uname").blur(function()
    {
                $("#msgbox").removeClass().addClass('messagebox').text('Checking...').fadeIn("slow");

        $.post("check.php",{ user_name:$(this).val() } ,function(data)
        {
          if(data=='no') //if username not avaiable
          {
            $("#msgbox").fadeTo(200,0.1,function() 
            { 

              $(this).html('This User name Already exists').addClass('messageboxerror').fadeTo(900,1);
            });     
          }
          else
          {
            $("#msgbox").fadeTo(200,0.1,function()  
            { 

              $(this).html('Username available to register').addClass('messageboxok').fadeTo(900,1);    
            });
          }

        });

    });
});
</script>

Upvotes: 2

Views: 489

Answers (6)

Jesse Bunch
Jesse Bunch

Reputation: 6679

You really need to focus on security, performance, and organization as a developer. Here's how I would re-write your code to be easier to read and more secure. Hope it helps you out:

<?php

$strUsername = isset($_POST['user_name']) ? $_POST['user_name'] : NULL;

if(!empty($strUsername)) {

    require("include/conn.php"); // Consider using require_once() if it makes sense

    $objResults = mysql_query(sprintf('SELECT userid
                                FROM vector.signup
                                WHERE userid = "%s"
                                LIMIT 1', mysql_real_escape_string($strUsername)));

    if ($objResults === FALSE) {

        // Log the mysql_error() (dont show it to the user for security reasons)
        exit('Insert user friendly error message');

    } else {

        if (mysql_num_rows($objResults) === 1) {

            echo "Yes";

        } else {

            echo "No";

        }

    }

} else {

    echo "Invalid input error message";

}
?>

Upvotes: 2

Raffael
Raffael

Reputation: 20045

you cannot optimize the query itself, as it is just searching fo the id in a column, and that's what has to be done. but you can add an index to the column, so your dbms is prepared to search the column efficiently. and you might choose to use iso-whatever instead of utf-8. then sorting will be faster for your db, b/c the field takes less memory.

Upvotes: 1

vbence
vbence

Reputation: 20333

You can delay your request to the server while the user types. The following example will call the myAjaxFunc function only when no key is presses for at least a second. (So no call-call-call while typing.)

<input type="text" name="username" onkeypress="ajaxRefresh()">

<script>
    window.ajaxTimeout = false;

    function ajaxRefresh() {
        if (window.ajaxTimeout) {
            clearTimeout(window.ajaxTimeout);
            window.ajaxTimeout = false;
        }
        window.ajaxTimeout = setTimeout(myAjaxFunc, 1000);
    }

    function myAjaxFunc() {
        // do your AJAX stuff
    }
</script>

Upvotes: 1

Collin
Collin

Reputation: 12267

Yes, you should probably add an index on the userid field. For mysql, that would look something like this:

ALTER TABLE `vector`.`signup` ADD INDEX(userid);

Edit: You can use this line in the MySQL console once you're connected to the database. If you're using PHPMyAdmin, it also supports adding indexes in their GUI.

Upvotes: 2

Chris Hutchinson
Chris Hutchinson

Reputation: 9212

Creating an index on the userid column of the vector.signup table will probably solve your performance issues.

As it stands you have a massive security flaw in your script. You should never inject POST data directly into a query, because you open yourself up to a SQL injection attack. You should escape your data first:

$user_name = mysql_real_escape_string($_POST['user_name']);

You can read more here:

http://php.net/manual/en/function.mysql-real-escape-string.php

Upvotes: 4

shashuec
shashuec

Reputation: 694

There are two use cases:

1)If you don't mind having list of users available to the end user who is searching for the username you can fetch the complete list in one go and use javascript function to give user the options on each key pressed.

2)If you don't want to give end user complete list ,you can be assured of the method you are using.Just have indexing on username.It will be sufficiently fast even for millions of records.

Thanks,

Shashwat

Upvotes: 1

Related Questions