Advice and Suggestions for Building MySQL Database, and accessing it with PHP?

mohamad_1978

Hi All..

I am designing a website that depends mainly on a MySQL database accessed using PHP scripts.

The site has just started but I expect the database to expand and become REAL big. and I need to plan it well from now to prevent any problems in performance/speed in the future.

I have two questions mainly:

1- What are the best ways to minimize the load on the Database? And To maximize the performance?

2- Say that I have a database of students (Example).. and I need to use PHP to get all the students whose ages range from 19 to 23.. Does it make any difference (on the load or speed of accessing the database) if the available students in the database were 10 or 1000000 ?

In Other words.. does the total number affect the speed and the load if I am only accessing a small portion of the database?

One more question please.. is there any limits for database sizes? is there a point where it starts to slow down and I should think about splitting the database? how can I measure this point?

Thanks for your concern.

superwormy

Obviously, the bigger the database gets the slower it's going to be / more CPU intensive / uses more RAM / etc. etc.

A million rows shoudl be no problem for MySQL as long as its not under a ridiculously heavy load and as long as you are using Indexes correctly.

What is REAL big by your definiation? 10mb? 100mb? 15 gigabytes?

Make sure you're normalizing data and keeping things organized, and yes the total number affects the speed no matter waht, albeit most of the time it doesnt matter. Don't forget that even on cable its going to take someone at least 2 to 3 seconds to recieve you page, benchmark your pages and see how long they take to actually generate.

Depends on teh database as far as the limit to the size of it, may also depend on the filesystem. It's usually a coupla gigabytes though...

stolzyboy

also depends on what you are doing with the database for if/when it gets doggy, if you are doing a lot of updating on a LOT of records, you may want to think about porting over to postgresql, mysql is a great database for selecting, but has some trouble with many, many records when updating and bad indexing is involved

mohamad_1978

let say i wana to make site for rateing books by the users u know people going to write what they think about the books and things

i will make table called (rate) and in this table

book_id .... user_id .... user_rate ... user_text

u know the book id is for the book number in our database and user_id is for the user number in our database

user rate is number from 0 to 10

user text is text for what user think

is it right to put all the rateing in one tables ?
i mean there is some books will have more then 1000 rates and when i will call them i will use

select * from rate where book_id='$bookid'

is it slow if i have alot of database by the time ?

u know if is my first project and i afread to have problim i have now in my site more then 6000 users and i think i will have more by the time

so what is the best way the best plane to have this rateing idea done without problim

i wish u get the idea and why im afread i dont know what is the big and what us not big in the database becouse as i told u im stell new

stolzyboy

you shouldn't have a problem with mysql for that, doesn't sound like you are doing any updating, mainly inserting and then displaying/selecting, i would stick with mysql in that situation, a little easier to work with than postgresql (IMHO), no need for database arguments.

superwormy

1000 records from a table is nothing for MySQL... but I hope for your user's sake that you're not intending on displaying all 1000 records at once, on one page, to your users. Thats going to be one massive chunk of HTML, its going to take forever to download and display, and who in their right mind is going to read through 1000 reviews anyway...?

You're probably going to do pages of 10 reviews per page or somethign, which will make for signifigantly nicer pages and smaller query result sets as well.

mohamad_1978

so u dont think i will have problim by the time ?

i mean when alot of users will add there rates ? and when users will add there rates it is updateing right ?

what about displaying ? is it going to take time by sorting and selecting if i have alot of data ?

superwormy

Everything takes time. Period. The more records you have, the more time it takes.

But a million records should be fine. If you ever end up with 20 or 30 million records and things get slow, you can invest time in working out how to cache queries / a different database format or a rewrite to make it faster.

I highly doubt you'll ever reach that limit.

When a user ads a rating, its an INSERT, UPDATE is just what it sound slike, UPDATEing a current record.

stolzyboy

i just don't think you are gonna have any problem at all with the numbers you are talking about, mysql will laugh at 1000 - 100000 records no matter what you are doing, but keep in mind what superwormy said, if you are displaying 1000's of records at once, you will see degraded performance, with any database, if you don't think out your queries and do them correctly you will see bad performance, but don't worry about it in your case with that little of records

ahundiak

You might consider building in performance tests right from the start. Get a time stamp when a page begins and when it ends then save the results in a logging table.

If it makes sense (depends on your code structure) then grab another timestamp after a page is done with the database and save it as well.

The idea is to build a little history so you can tell if the site starts to slow down. And you can teel if a given code change impacts performance.

mohamad_1978

thanks guys for all the info

ahundiak i really dont understand ur idea im sorry but as i say im new in the database programming

ahundiak

I didn't explain it very well. Basically, have your pages time how long it takes them to run and store the results in a table or file. You would then periodically review the table. Maybe a page started out taking 1-2 second to run. But over time it starts to take 3 then 4 then 5 seconds. Now you know you have a problem somewhere and can take action.

mohamad_1978

thanks alot i think i get the idea
i will start build the database today
thanks for the help