StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHelp with a SQL querying speed and performance issue
text
Body
copied!<p>I've got a classified listing website for cars which I'm in the process of developing built in PHP. The user enters the details of the car they are looking for using drop down option boxes on the homepage. When they click submit they are taken through to the results page, and this is where I'm having problems. </p> <p>The way it is set-up at the moment is:</p> <ol> <li>The database is queried for any results matching the car they are looking for. The query returns the ID of the car and the Postcode of the advert; </li> <li>Each advert is then checked for the distance between the users postcode and the postcode of the advert. This itself requires a database query to find the coordinates for individual postcodes of each advert and is quite time consuming for what can be in excess of 350 results at times;</li> <li>An if statement is then used to determine if the distance is less than or equal to the distance the user entered on the homepage</li> <li>If the advert is within the allowed distance it's ID is added to an array;</li> <li>The total number of adverts in the array is then calculated and used to determine a variable dependant on the number of adverts and the number of adverts to be displayed on the page;</li> <li>A second query of the advert table is then executed using a <code>WHERE</code> statement and the ID's in the array. e.g. <code>SELECT * FROM adverts WHERE ID=1 AND ID=4 AND ID=23</code> ........ The total number of ID's used in the query depend on the variable mentioned in point 5. When the user then clicks next page the query is re-run from the position in the array that it was left at and the query is then re-created and executed.</li> </ol> <p>The problem I'm having is that it is taking ages to complete and I was looking for a more resource and time concious way of completing it. </p> <p>It originally was designed that a query would execute with WHERE clauses for each of the users specific requirements for the car, and then before being output to the page the distance was being checked using an if statement. This caused problems with the page numbering because it was impossible to determine the number of adverts that would match the distance requirements from the adverts returned in the query- hence it is done this way with the distance conditions being satisfied before the full adverts are collected so an exact number of adverts to be displayed is calculated.</p> <p>Sorry its a little long - hope it makes sense. I haven't included any code because it would make it longer, and its a problem with the logic as opposed to the actual code. </p> <p>Thanks for any suggestions you are able to make.</p> <p>Someone has requested the table layout and SQL. Here goes.....</p> <blockquote> <p>Advert Table </p> <p>ID, Make, Model, Colour, Mileage, Engine, Year, Postcode</p> <p>Postcode Table</p> <p>ID, Postcode, GridN, GridE, Longitude, Latitude</p> </blockquote> <p>SQL for first query to get the ID and Postcode</p> <p><code>SELECT ID, Postcode FROM adverts WHERE Make = '$subMake' AND Model = '$subModel'</code> etc</p> <p>SQL for the second query to get the advert details using the ID's that match the distance requirements: </p> <pre><code>SELECT Make, Model, Year, Engine, Colour FROM adverts WHERE ID IN(1,2,6,90,112,898) </code></pre> <p>(Sorry if its not syntactically correct, it does work, that SQL is just a rough outline of the many lines of the query strings.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload