How can I better optimize my PHP and MySQL Queries?

Question

I have a website I'm working on and it all works just fine. I'm concerned that under load the way I am doing things currently could cause some server lag.

In the database I have a table with a lot of information in it (14+ columns and several hundred entries). The PHP page has a script on it to generate page links (Page 1 of 6, next page, previous, first, last, etc.). It currently displays 25 entries per page. The script multiplies the page number by 25 to get which entry in the table it needs to start on for the select.

That's not too horrible I don't think. The real issue comes from the "Page x of y". Meaning that I need to know how many pages of items there will be, or how many times 25 goes into the number of entries. I've done this by using something like:

SELECT count(id) FROM `footwear`

to just get the count of entries in the table. Then using that count I can calculate all my pages. Is that the most efficient way of doing things? Or perhaps there's a better way to calculate the page count?

Looks good. With the number of records in the hundreds, you shouldn't have performance problems in normal circumstances. — ironcito
– ironcito, Commented Aug 1, 2013 at 5:51
don't think this will slow you down noticeably even with a million records. — bansi
– bansi, Commented Aug 1, 2013 at 6:02

faazshift · Accepted Answer · 2013-08-01 05:55:22Z

1

I might recommend query caching in MySQL to improve general performance.

Here's a guide to enable that: http://www.cyberciti.biz/tips/enable-the-query-cache-in-mysql-to-improve-performance.html

answered Aug 1, 2013 at 5:55

faazshift

4854 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sharf Over a year ago

Everyone answered my question, but you get the answer for providing a link. Thanks for the tip.

Mikhail · Accepted Answer · 2013-08-01 05:50:22Z

1

Just make sure you have an index on id and it'll be very fast in MySQL.

If you suspect that it's slow you can always measure it to calm your mind.

answered Aug 1, 2013 at 5:50

Mikhail

9,0569 gold badges58 silver badges88 bronze badges

Comments

Jose Sutilo · Accepted Answer · 2013-08-01 05:52:34Z

1

If you are using MyISAM, then you are fine, if you are using InnoDB as your storage engine, you might want to cache the number of rows or store it on a separate table, the reason for that is that MyISAM will keep record of the number of rows on the table but InnoDB will count all the rows everytime you do the query.

I hope this helps.

answered Aug 1, 2013 at 5:52

Jose Sutilo

9181 gold badge5 silver badges7 bronze badges

Collectives™ on Stack Overflow

How can I better optimize my PHP and MySQL Queries?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related