Getting performance right...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
eromein
Forum Newbie
Posts: 7
Joined: Fri May 06, 2005 7:35 am

Getting performance right...

Post by eromein »

Hi,

I've asked this question before but didn't get an answer. In the mean while I've been searching to find an answer but haven't found one yet.

Could somebody please take the time to read this post and maybe help me on my way? Thanks!

I'm using the following query:

SELECT table_id, company_name, record_id FROM tabledata WHERE record_id IN (SELECT record_id FROM tabledata WHERE (field_name = 'no' AND field_value = '1001')) GROUP BY table_id, company_name, record_id

* The table I'm quering could hold thousends of rows.

I'm showing the result on a page with a pageslider (<< < 1 2 3 4 5 6 7 > >>). This pageslider need to know howmay records in total there are to calculate howmany pages there should be, so it can show the correct number of pages in the slider.

Here is my problem / question.

I'm using the slider to increase performance. I'm thinking, why get all records / rows when I only want to show, lets say, the first 20 on page 1. So, I want to limit my query BUT I do need the total number of row to get my slider work correctly.

1.) What do I need to do in this case? Do I first need to do a COUNT to get the number of rows?

2.) Do I really improve performance with this (I need to do the query twice, ones to count ones to get records)?

3.) Is there another way to deal with these problems? How do you guys solve these issues.

Hope you will try to help!

Thanks,

Emiel Romein
Netherlands, the
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

The number of rows in the db won't really matter to much.
Yes you are right that return the complete result set could slow things down.

They way I solve the problem is to do one query on a field that has been indexed and do a count on it or a simple mysql_num_rows();

Then run the query agian with a LIMIT clause tacked onto the end.

from there create the pagination required from the count.
Post Reply