MySQL Forums
Forum List  »  Performance

Optimising Time to Output Listings from a Search
Posted by: Ken Dawber
Date: March 01, 2005 12:10AM

I have recently taken over maintenance and development of an Open Source PHP/mysql package used for creating web sites for real estate agents. This package is called Agent-See While I have had many years experience in programming, I have had relatively little experience with PHP/mysql.

With Real Estate web sites, it is common for users to request searches where thousands of properties (eg houses for sale or rental properties etc) can be returned. The output is then displayed in web pages displaying typically 10 of these properties per page with links to the next few and previous pages along with a display of number of properties meeting their search criteria. At present, the data for this all comes from querying a single properties table. The code (not written by me) is of the form

$result = mysql_query("$querystring;",$link);
$num_rows = mysql_num_rows($result);

// Use $num_rows to display links to previous and following pages etc near top of web page.

$query = $querystring . “ LIMIT “ . $first_property . “ , ” $properties_per_page ;
$result = mysql_query("$query;",$link);
while ($a_row =mysql_fetch_array ($result) ) { ……

I'm interested in reducing the time it takes for the user to start to see the listing.. I have identified 10 ways that I believe I can speed this up through changing the software.

A number of the methods have been created due to my belief that, at the present time, the vast majority of time taken. is taken up in calculating the number of properties that meet the search criteria. In particular, most of the time is taken up in performing the first statement in the above code. I haven’t tested this belief but would like to know from more experienced PHP/mysql programmers whether they agree?

I want to know if each of the following methods appears sensible to experienced PHP/mysql users? Also, are there additional methods that I have missed? My 10 methods are as follows

i) Use a fixed length properties table with all fields being fixed width. (possibly with current properties table split into 2 tables with the variable length data, that is only used for a complete listing of a single property, being placed in a separate table).

ii) Use of indexes on most columns used for search criteria. Note: Columns such as number of bedrooms or number of bathrooms probably don't deserve to be indexed as these criteria generally won't reduce enough the number of rows returned.

iii) Use of known quantities, calculated while data is input, relating to the properties data (eg total number of properties in database, number of properties in each district etc) to precalculate the number of items that will be in a listing. Hopefully this can be done for any search where no more than one of the possible search criteria is specified.

iv) Where above quantities are not known (i.e multiple seach criteria requested), defer showing how many properties meet the search criteria until we have reached the bottom of the first page of listings (i.e next page(s) links only at the bottom of this listing page).

v) When giving second or later pages of a listing, use the number of properties meeting the search criteria that was calculated when outputting the first page. (stored in a $_SESSION variable) (Note: Now able to have links to following and previous pages plus show total number of properties that meet their criteria at both the top and bottom of these listing pages.)

vi) Above a certain quantity such as 1000, simply give number of properties meeting the search criteria as being greater than 1000. (queries used for calculating the number of items that meet the search criteria will always have a 'LIMIT 1001' added on to them)

vii) Use of "$result = mysql_unbuffered_query(…)" to display the page as it is being calculated.

viii) Use "while ($row = mysql_fetch_assoc($result)) ..." in conjunction with mysql_unbuffered_query(), to continue, after the current listing page is displayed, to get row data relating to the following page or pages. The row data for these follow on pages is placed in $_SESSION variables and this data is used to display the next page or pages if or when these pages are requested. Note: The row index 'i' used here is to specify a limited number of storage rows in memory. There needs to be a translation between this number and the actual row number within the search listing. This is required to ensure limited use of memory.

ix) Splitting the single properties table currently used for multiple purposes (eg rentals, land for sale, farms or businesses for sale, commercial real estate etc) into separate tables.

x) Use of the $querystring in the first line of code shown asks for all the fields from the query to be copied from the database and placed in a memory resource. It would appear to me to be a lot of copying of data fields which will never be used. When performing a query which is just used to get the number of rows, can we use a “SELECT FROM properties” without specifying any fields? If not then we should only specify a simple id field for this!

A running copy of the real estate software can be seen at

http://agent-see.com/as1

The full source software can be downloaded from

http://sourceforge.net/projects/agent-see


Thank you for your comments

Ken Dawber
Melbourne Australia.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimising Time to Output Listings from a Search
2482
March 01, 2005 12:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.