0

I have a query that starts pretty simple:

SELECT zudf16, zudf17, zudf18, zudf19, zudf20, zitemid, zItemName, zPhotoName, zBasePrice FROM zskus WHERE zmanufacturerid=? AND zudf16=? ORDER BY zItemID

As choices are made I build on it:

SELECT zudf16, zudf17, zudf18, zudf19, zudf20, zitemid, zItemName, zPhotoName, zBasePrice FROM zskus WHERE zmanufacturerid=? AND zudf16=? AND zudf19=? AND zudf20=? ORDER BY zItemID

For each parameter I add the query drops in performance, especially on iPad1. Each of these columns have an index on them.

How can I increase the performance of this query as it get's more targeted rather than lose performance?

This is across around 60,000 records that match the initial ManufacturerID but with a total of about 200,000 rows total in the database.

icodebuster
  • 8,890
  • 7
  • 62
  • 65
Slee
  • 27,498
  • 52
  • 145
  • 243
  • Are you are always filtering down from the initial query each time? – savner May 28 '13 at 15:36
  • no exactly, there is a list of 5 options and they can add and remove them as they wish, I thought of just searching the results every time they add a filter but if they pick filter 1 and then filter 2 and then remove filter 1 and choose filter 3 I have to start from scratch but that might be my best best - initial search on 1 filter and then do then filter the rest from the result. – Slee May 28 '13 at 16:31

0 Answers0