database optimise

[praveen_dusari]

hi, i have developed a shopping cart using php mysql, it has around 50 products in home page its taking too much time to load can anyone suggest how to optimise my database so that it takes less time

[cwarn23]

A common problem we all experience when a noob to displaying huge databases. This is where you need to use pagination. Try Googling "php pagination" as i've rarely used it myself and the above link contains an example.

[praveen_dusari]

i have done navigation many times but i donot need pagination,i have done many ways to reduce the burden on database like instead of using select * i used select id,name,price like that.i takes more than 10 sec to load on fast internet too i personelly think it too much time any ideas?

[cwarn23]

Well from experience, if you have a database that is longer than 80 rows then that is when pagination will be needed. The only thing that is stopping it from loading faster is the cpu speed. Weather php is only using a proportion of your cpu or the job exceeds the cpu available, it is generally cpu that determines the time for a page to execute (and the rest eg. the right functions). Also just as a note to speed things up, instead of using mysql_fetch_array(), use mysql_fetch_assoc(). That should speed things up by about half a second.
I remember when I had a database with 21000 rows and 8 columns it took about a minute to load because pagination was needed.

[praveen_dusari]

correct me if i am wrong,i my office all systems are equally configured
and our team leader uses internet which is much faster than us in his system my page took 8 sec to load and in my system it took more than 10 sec to load ,it depends on net speed i think,
and is there a concept like compressing php page in server and sending it to browser?

[cwarn23]

is there a concept like compressing php page in server and sending it to browser?

I'm not sure if anybody has thought of that before but that's a great idea for sending large amounts of html data to the user. I suppose you would use php to encode the data and javascript to decode the data. Never thought of that before.

Anyway, as for your other question for if internet speed is a factor. That is only true if the file size exceeds your internet speed. So for example, the most common internet speed is 25kbps which means that a file 25kb would take 1 second to load. In your case, if you download your php file and it turns out to be 200kb and you devide that by 8, if the answer of that sum = your internet speed then it would indeed be the speed of the connection making it slow. So below is the formula to calculate if it is your internet speed limiting the page load time.

x = internet speed in kb per second
y = time for page to load in seconds
z = size of page
if ((z/y)==x) {
internet speed is limiting the
page load time
} else {
you have sloppy/slow coding
}

[kireol]

I would start by reporting the timing of my queries. I assume you are doing more than 1 query. Record the start and end times of each query. Then look at the slowest query, paste it here, and see if people can help you optimize it.

[praveen_dusari]

i used only one query to show the products.is there no other techniques to load the contents faster other than using less queries
may be i should use more div tags and css style sheets :-/ rather than using tables may be this will work

[kireol]

1 query should take less than 1/4 of a second at most. not 8 seconds.

[praveen_dusari]

yeah query takes less than a sec but to load contents like product images,it takes time, it should load around 40 images
thats why i asked is there any method in php that will compress before sending..

[Josh Connerty]

So it is infact your HTML document and not the PHP?

To test if your PHP document is taking a very long time try taking down a time stamp at the start and the end of your PHP document.

Then at the end minus the start from the last. IF this returns false then your script is executing like it should. If it is a second or more (if the result of the maths is 1 or more) then PHP/MySql is under strain from the host machine.

If your script is executing like it should then yes it is indeed your HTML contents that are causing the long load. It is supprising that you ae not already cropping your images whern they are uploaded.

It is highly advised you crop them once and not every time you display them as this WILL cause PHP to be laggy.