Product Sort Order

[davem]

I wanted to give customers the opportunity to sort product listings by price, either low to high or high to low. This is not quite as easy as it sounds as some of my products are subject to tax (vat) at 17.5% while others are zero rated. There is also the issue of special prices (again vat on some no vat on others).

My work around to achieve this included adding a parameter to the query string (which I named pp_so) and modifying the sql along the lines of the one used on the advance search page when min/max prices are included.

It all seems to work ok but the results can be very slow, particularly is there are quite a few products in a particular category or from a particular manufacturer. This is not surprising as the listing sql needs to select all products and sort them before splitting them into the relevant order/pages.

As an example of what I mean, please go to:

www.pramsplus.co.uk

Select Red Castle from the manufacturers dropdown box in the left column. I have set it up to show the listing_sql used. It will initially use the default (by name) which is fine. If you then select sort by price you will see the revised listing_sql and what I mean about it being slow.

Can anyone suggest a better way of doing this or how to optimise the sql?

When the site is finished some categories and manufacturers will have many more products than the number in the example, so my work around will probably be unpractical given the time delays.

Many thanks

[michael_s]

Have you tried adding an index to the products table for the prices? That may help...

[davem]

Michael,

Thanks for getting back to me. What you suggest is one of the ways I was thinking about going with this. My thoughts were to add a field (say sort_price) to both the products and specials tables. To do it properly I will need to revise the update sql in the relevant admin files so it automatically calculates and inserts the sort price when products are added and/or modified. I also use easypopulate so I would need to modify that as well.

I was just wondering if anyone with a far better knowledge of sql than me could suggest how to do it "dynamically" with an acceptable load on the server.

Again, many thanks

[michael_s]

I think just adding an index or two to the table, rather than adding a new field should greatly improve the performance. See the mysql website for information on indexing, as it has nothing to do with adding fields or changing sql queries...

[davem]

Michael,

Many thanks for pointing me in the right direction. Have added a couple of indexes and cleaned up the sql which has much improved the query execution speed.

For anyone with a pretty basic knowledge of sql (like myself), I found the following article on indexing quite helpful:

http://www.informit.com/articles/art...77652&rl=1

Again, many thanks