Use session variables or multiple sql queries?

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
csingsaas
Forum Newbie
Posts: 22
Joined: Thu Jul 06, 2006 9:34 pm
Location: Edina, MN

Use session variables or multiple sql queries?

Post by csingsaas »

I am writing a script to search a database I have put together. There will potentially be thousands of records. If someone did a rather broad search, there could be 2,000 to 3,000 matching records. The results will be displayed 15 per page. The search window allows the user to define up to 5 different fields. As an example, if we are talking about cars - the user can define:

location of car (by state)
model of car
price of car
color of car
year of car

* some fields allow multiple selections (such as year, color, model, location) *

Which of the following options am I better of implementing in terms of site performance (assuming that traffic will be around 1,000 unique visitors per day).


1) A user enters a search. I save the results of the search in various session variables (arrays). As the user browses through each page, I access the the arrays as needed. So for the second page I display (again, assuming I am displaying 15 records per page) the index I will need to use to extract the array information is 15 - 29 (i.e. $_SESSION['car_name'][29]; ).

2) For each page that is displayed, perform a new SQL query. The limits in the query would change depending on the page number I am looking at. For the second page of search results I would set the limit to 16 - 30.



Bottom line - should I perform a bunch of SQL statements frequently, or - do the query once and store it as a session variable. Any thoughts you can offer on this would be appreciated.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

I would do a combination of the two. If you have a lot of records on the database, obviously searching broad terms is going to take a bit of time. However, storing a bunch of row information in sessions is going to cause a hit as well. What I'd do is return my result set and store the ids of those rows in the session. That way you can pass it from page to page and still maintain a relatively low profile on your resources.
TheProgrammer
Forum Newbie
Posts: 22
Joined: Mon Nov 27, 2006 12:25 am

Post by TheProgrammer »

Hmmm..if the database is optimized corectly, you use indexes properly and have a LIMIT clause and avoid to use variable lenght fields in a thousand of rows table the search should take just miliseconds. Also use EXPLAIN on the sql query to see what you might need. So i'd go for the sqls querys. Of course you should also check the server capabilities. I worked with tables with hundred of thousands of rows (17GB) and if optimized right there was no problem. At least you don't risque having session variables refused.
Post Reply