Do I need to create Index seperatly for MySQL??

Question

I am facing some problem with MySQL database. actually It was fast when I did testing on My localhost..but when I uploaded to public server it is slow. data s are displaying but taking more time than usual. there are 7 table which opens to read for a single page. so what I did is for first table I added username and password. for last table I am closing database using mysql_close($db); command.

is this the right way to do it?? I haven't much experience in database. I have used simple method for calling data.

$db = mysql_connect("localhost", "username", "password") or die("Could not connect.");
mysql_select_db("database",$db)or die(mysql_error()); 
$result = mysql_query("SELECT * FROM table WHERE web='$data'")or die(mysql_error());
$row = mysql_fetch_array($result);
$daysA = $row['regtime'];
$days = (strtotime(date("Y-m-d")) - strtotime($row['regtime'])) / (60 * 60 * 24);
if($row > 0 && $days < 7){
   $data = $row['data'];
   $data1 = $row['data1'];
   $data2 = $row['data2'];
   $data3 = $row['data3'];
   $data4 = $row['data4'];
}else{ 

//some function to fill database }

then at end of 7th table I added database close mysql_close($db);

Please help

Answer 1

Do you have an index on the "web" column?

mysql_close is not related at all, PHP will automatically call that function at the end of the script if you forgot it.

Answer 2

Not sure that I quite understand your question, but here are two general advices considering SQL queries :

• When a column is used in a condition (i.e. where clause), it's often a good idea to put an index on it : it will often allow MySQL to find the lines that match the where clause without having to scan the whole table.
• If you are using 7 queries, would it not be possible for you to reduce that number of queries ?
  • A solution, often, is to use some joins between tables.
  • Another could be to add some caching mecanism

Here are a couple of sections of the MySQL manual that might be interesting for you :

• 12.1.13. CREATE INDEX Syntax
• 7.2. Optimizing SELECT and Other Statements

As a sidenote : make sure you properly escape $data before injecting it in the SQL query, to avoid SQL Injections ;-)

Answer 3

Another optimization you can use is to move the date calculations into the query:

$days = (strtotime(date("Y-m-d")) - strtotime($row['regtime'])) / (60 * 60 * 24);

You're forcing the server to do double-duty here: MySQL has to convert the 'regtime' date/time field from its internal format into a string, and then you convert that string back into a PHP time value (which is just an integer). MySQL is perfectly capable of doing date/time calculations itself, and you can avoid the double conversion overhead.

comment followup:

Looking at that chunk of PHP code, it boils down to:

date('Y-m-d') - since there's no time parameter provided, it defaults to today's timestamp (1277186400), and generates 2010-06-22 strtotime('2010-06-22') - converts BACK to a unix time stamp, 1277186400

and then

strtotime($row['regtime']) - convert the string representation of whatever date is stored in the regtime field into a unix timestamp. Let's say regtime is 2009-12-24 (hey, it's Christmas again!), so strtotime returns 1261634400

so you end up with:

$days = (1277186400 - 1261634400) / (60 * 60 * 24)
$days = 180; // 180 days between Christmas and today

So, on the MySQL side of things (assuming that the regtime field is at least a 'date' field, or datetime/timestamp:

SELECT (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(regtime)) / (60 * 60 * 24) AS days
FROM ...

or, since you're working with days as the interval:

SELECT TO_DAYS(now()) - TO_DAYS(regtime) AS days
FROM ...