If I have a few dozen webpages that have, say, 5000 characters in them, is it "easier" (sorry I don't know a more technical term ) for the webserver to pull the content from a table in a mySQL database and then "echo" it out on the page, or to have a static html page? Is there a significant difference? I would kind of like to store my content in columns in tables in a mySQL database because I can manipulate the data before outputting it to the page, but I don't want to bog down my server.

Thanks for your advice!

static page would be easier of course

Is there a significant difference, though? I mean, will I be putting a lot more stress on the server by storing the pages in a database?

Yes, reading in data from a database puts more 'stress' on the server. The amount of stress depends on a lot of factors, including how well you implement your script and design your database. Clearly though, the amount of stress isn't prohibitive considering that the vast majority of sites you visit are using some form of database to store content. Every post in this forum, for example, is stored in a database (over 1 million of them total according to the stats in the footer).

So, if I'm making a query and I just need one column from a row in a table, would it be a lot better to use a query like this:

'SELECT age from fruittable WHERE fruit = orange' // this reads one cell?

as opposed to this

'SELECT * from fruittable WHERE fruit = orange' // this reads the whole row?

If the database server is ON THE SAME machine it may take marginally more resources to produce the page.

BUT by NOT using inefficient "SELECT * FROM" the difference will be negligible unless the hardware is grossly underpowered.

I think the database is on a different server.

BUT by NOT using inefficient "SELECT * FROM" the difference will be negligible unless the hardware is grossly underpowered.

So what should I use in place of the "SELECT * FROM" statement?

In general it is a good practice to always make a query do as much as possible, to keep the amount of transmitted data small. Moreover, using the `*'-symbol hides the implementation of the table, so its behaviour may change when you ever decide to change the structure of the database. This may be bad for code visibility.

So what should I use in place of the "SELECT * FROM" statement?

A comma seperated list of the columns you need to return in the recordset, using * forces two passes of the tables, one to read the column names and their index number, with the second pass to retrieve the data from the rows.

A comma seperated list of the columns you need to return in the recordset, using * forces two passes of the tables, one to read the column names and their index number, with the second pass to retrieve the data from the rows.

So even if I'm selecting all of the columns in the row, I should use the comma separated list in the query?

Say I have the following table named "fruittable":

 index         fruit               age            origin
int(10)      varchar(50)        varchar(50)     varchar(50)

Just to make sure I understand, to get one field, say "age", from the row where the "fruit" equals "orange" (only one column out of the row) I use:

$query='SELECT age from fruittable WHERE fruit = orange';

To get all the cells (all the columns in the row), I use:

$query='SELECT fruit, age, origin from fruittable WHERE fruit = orange';

Then in either case, to get the actual values do I go:

$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);

Then the value(s) would be in the array which I could then access?

Thanks for helping me understand

I should use the comma separated list in the query?

Yes

Just to make sure I understand, to get one field, say "age", from the row where the "fruit" equals "orange" (only one column out of the row) I use:

PHP Code:

$query='SELECT age from fruittable WHERE fruit = orange'; 


Yes

To get all the cells (all the columns in the row), I use:

PHP Code:

$query='SELECT fruit, age, origin from fruittable WHERE fruit = orange'; 


Yes

Then in either case, to get the actual values do I go:

PHP Code:

$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM); 


Then the value(s) would be in the array which I could then access?

By using MYSQL_NUM as the array type you would access the results using ONLY the index numbers, eg: $row[colNo] where the index numbers are in the order that you specified the column names in the query (zero based).

Using "SELECT *" returns them in the order they are in the table, so if your later code then uses $row[n] and the table has been restructured for some reason, the output from your code will be in error, and in the best case it will crash, with the worst case scenario of giving erroneous calculations that are not detected.

Thanks for explaining that! It makes sense now

By using MYSQL_NUM as the array type you would access the results using ONLY the index numbers, eg: $row[colNo] where the index numbers are in the order that you specified the column names in the query (zero based).

Is MYSQL_NUM more efficient than MYSQL_ASSOC, or is there no difference?

Using "SELECT *" returns them in the order they are in the table, so if your later code then uses $row[n] and the table has been restructured for some reason, the output from your code will be in error, and in the best case it will crash, with the worst case scenario of giving erroneous calculations that are not detected.

So the moral of the story is to always use a comma separated list?

One last question; is there a big difference performance-wise between selecting a single column from a table as opposed to selecting the whole row?

Thanks for all of the help and advice!

Originally Posted by Learnin' n00b

Is MYSQL_NUM more efficient than MYSQL_ASSOC, or is there no difference?

There isn't any good reason to concern yourself with such a minor performance difference. Good design dictates that you should use ASSOC in most cases since the string indexes will be meaningful.

Originally Posted by Learnin' n00b

One last question; is there a big difference performance-wise between selecting a single column from a table as opposed to selecting the whole row?

Only select what you need. As far as I know there is no significant performance difference between SELECT * and specifying every column. I could be wrong, but in any case it is best to be as specific as possible.

"Only select what you need" is good advice - second bit of advice is to make the query do any processing whenever you can, for example ordering results in a certain way.

The reason you select as little as possible comes into play when you're querying large tables with lots of data to return - then you could find the db having to do lots of disk access and messing around with temp files before it returns the results. Probably not an issue for you but following best practice is always a good idea.

I actually have better luck with static html pages rather than dynamic php database pages.

Better "luck"??????

I find it easier to do a database site then doing it all through straight HTML. Mainly because instead of opening every html file and going to the correct lines and so on i can just open up PHPMyAdmin or Navicat or some other program to manage my SQL and go right to it quick and do the edit. If i do not do that i usually just code a edit form to update my pages with what i want.

I also find it easier on the system not a whole lot easier but it does run quicker if you tell the query what you are pulling vs *. Not also to mention if i am correct it also helps limit what shows if there is a flaw in the script to only show specific information instead of the whole row.

Also consider this:

• INDEX you db columns:
  Say you have 1000 records with a column ´fruit_id' that record either one of: apple (fruit_id 1), pear (fruit_id 2), orange (fruit_id 3).
  |fruit_id|
  1
  3
  1
  2
  1
  1
  2
  2
  ....
  If you INDEX the column, the database query (let's say we are querying apples), will look for INDEX 1 and skip all other records.
  You can put INDEX on any column where it makes sense (do it from phpMyAdmin).
  http://www.tizag.com/mysqlTutorial/mysql-index.php
• I know that people say that closing db connection is not necessary. I think it is. Close your connections as soon as possible.
• I am doing a daily CRON JOB on my database that REPAIR and OPTIMIZE my database tables.

All together these simple procedures prevent the database to OVERHEAD.

yeah, i do agree too.. database, that is..

you see, if you really want to manipulate your content before you output them, it's difficult to maintain your website.. by using a database, (of course, with proper codes), you can make it work..

Do your best!