Do I need to create Index seperatly for MySQL?

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 en开发者_Go百科d of 7th table I added database close mysql_close($db);

Please help

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 ;-)

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.

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 ...