trying to 'future-proof' my site

[codebyren]

G'day helpful people.

 

I have been working on a site for a while and am struggling with some "future-proof-ness" aspects of it - particularly related to server strain. I am pretty new to php/mysql so feel free to blast me if my concerns are stupid etc. Just make sure that in your yelling and cursing, you tell me WHY they are stupid. Thanks.

 

- = the problem = -

 

I have a page that displays a requested posting made by a user (think of it as a blog if you must). On this page, users are able to choose to see other postings from the same (a) user (b) category © series  (d) etc.

 

Currently, I run mysql queries to count and display how many postings exist in those options - e.g. Six other postings may exist by the same user, five in the same category and three in the same series. This means that there are at least 4 queries on this page alone - including one to get the initially requested posting.

 

SO: Would it be better to just have one query that places the whole 'postings' table from mysql in an array and then manipulate that array to find more by the same user, category, series etc? As the user content grows, my guess is that this array could use up quite a large amount of server resources - but at the same time, isn't having too many queries a bad thing too? several users looking at the same page with several queries means a heck of a lot of queries for the server to handle... I think.

 

I have no idea what typical servers are capable of. I have been looking at godaddy.com for basic shared hosting and I'm sure that will be fine to start off with.  I just want to design the site in such a way that it can grow gracefully as I upgrade servers, enable mysql query caching etc. (no idea how to do this yet). My site in question does do a bit of image resizing but other than that, I'm just trying to keep server strain to a minimum...

 

Basically I'm looking at some input as to how to limit server strain in this type of situation/website.  Please let me know if you need any other info to help.

 

Thanks for reading my huge chunk of text... and for any assistance.

[PHP_PhREEEk]

There is no clear answer to your question, as it has too many unknown variables associated with it (as any future predictions would have...).

 

Understanding the MySQL engine would help here. It's not the number of querys that matters (typically), but the efficiency of those querys. Getting back to the understanding part, utilizing correct indexing and optimizing querys can make the difference. In other words, one inefficient query can take longer than 100 efficient ones. Try to limit SELECT * (all fields) to only when you actually need all the fields. Typically, this would be to load up an array to use around the script. In most other instances, it would be best to request only the actual fields you will need for that particular use. Fields that are used to narrow down querys, such as WHERE etc., should have indexes on the table. Correctly using JOINs and UNIONs, and having the correct indexes for any JOINs is more efficient, too.

 

Within all of that lies the most important thing you can do to optimize MySQL: correct database normalization

 

This is arguably the hardest part of mastering any database engine, but you absolutely cannot create the most efficient query without it. A poorly designed database will have inefficient querys associated with it, every time. As you can now start to see, this is all a delicate dance between a correctly normalized database and using efficient querys to access the data required. That you need 4 querys per page, or 8, or 800 doesn't matter. If even one of them is what is considered a 'slow query', you're in trouble, and that trouble will only get worse as the database grows larger.

 

Hope that gives you a few things to think about and absorb...

 

PhREEEk

[codebyren]

Thanks for the input:

 

Hypothetically, the mysql tables wouldn't get much more complicated than:

 

post_edtails

[post_id] [user_id] [post_title] [post_subtitle] [category_id] [edit_time]

- post_id is primary key and auto increment

 

post_body

[post_id] [post_body]

- post_id is primary key.. I guess.

 

categories

[category_id] [category]

- category_id is primary key.

 

What sort of damage is a few queries such as the following likely to cause? The database structure seems pretty logical to me? It's been a little while since I've read anything on normalising etc.

 

SELECT `post_title`, `post_subtitle`,  `category`, `edit_time`, `post_body`, `category`
FROM `post_details` NATURAL JOIN `post_body` NATURAL JOIN `categories` WHERE `post_id`='something'

 

I realize this is still pretty vague and general - it's just hard to get an idea of what servers are capable of, what really pushes them to their limits - and what sort of queries to steer clear of... I'm working with xampp locally at the moment so of course everything is pretty much instant...

 

Thanks for the input though, I'll look up more on good mysql practice...

[PHP_PhREEEk]

>What sort of damage is a few queries such as the following likely to cause?

 

You can get that information yourself by using EXPLAIN to have MySQL tell you how it will go about satisfying a query..

 

EXPLAIN SELECT `post_title`, `post_subtitle`,  `category`, `edit_time`, `post_body`, `category`
FROM `post_details` NATURAL JOIN `post_body` NATURAL JOIN `categories` WHERE `post_id`='something'

 

'something' must be a valid value

 

You should google how to use and interpret EXPLAIN output. It is the tool of preference for optimizing querys.

 

PhREEEk

[revraz]

You should also google on how to use JOIN.

[codebyren]

'something' must be a valid value

 

Haha, I guess I should have cleared that up. It would definitely not be 'something' literally. It would be a post_id value like '7' etc.

 

Thanks for putting me in the right direction though. Will definitely look into EXPLAIN and using more appropriate JOINs.