Hi everyone,
and thank you for reading, im sure that any assistance you may provide will be very helpful.

Im currently developing an app that is effectively a property management system. On search pages for property I have two queries:

This query executes and pulls of our list of properties.

SELECT property_short_name, property_town, property_postcode, property_id 
FROM property 
ORDER BY property_short_name 
DESC LIMIT " . $previousPage . ",10

Now obviously im limiting it to 10 results to prevent screen flood, so i perform another query so that i can build a Page X of Y at the footer.

This query is used with mysql_num_rows() to tell me how many properties are on the system.

SELECT property_id 
FROM property

The first query runs off very quickly as it is only 10 items, however the second query is taking quite a well to execute as there are a considerable amount of entries.

My question: Can anyone advise or give opinions to speeding up the second query?

Thanks,
Neil

    Could try ...

    SELECT COUNT(*) FROM property

      if you add SQL_CALC_FOUND_ROWS to the first query you don't need a second one to do a count

      mysql_query('SELECT FOUND_ROWS( )'); will return the count

        Ty for the replies, i will try out both options tomorrow. Although dagon wont your solution only return the found rows according to the query? I.e. it fou.d 10 rows.

          neilmasters;10959169 wrote:

          Although dagon wont your solution only return the found rows according to the query? I.e. it fou.d 10 rows.

          I can answer that. The whole point of that function in mysql is that it returns the row count as if there was no limit used.

            yeah what he said :-) i use if for every pagination script, to get the total, which is what your doing.

              Paulnaj, ty for your advice. I tested this and while it does offer a small amount of performance increase from 1.02234 to 0.9342 the query is still effectively running through the whole database.

              Dagons solution has improved performance massively as the individual queries were not a problem, so if i could piggy back on that one single query and ignore the limit it would save me a massive amount of resources.

              Before: 1.2234
              After: 0.0593

              Nuff said really.

                Wow, that's a massive difference.

                Will be implementing Dagon's method myself from now on!!

                  The performance increase is awesome.

                  In my case the first query was limited to 10 which is nothing in comparison to the second which is going through the whole table of about 2000 properties doing a count as it goes along.

                    Write a Reply...