I'm writing a pretty basic SQL dynamically to build a search based on the users criteria. A simplistic example is below.... SELECT dbo.tbl_Products.[Name], description, RANK FROM dbo.tbl_Products INNER JOIN FREETEXTTABLE(dbo.tbl_Products, SearchField, 'some product') AS KEY_TBL ON dbo.tbl_Products.ProductId = KEY_TBL.[Key] ORDER BY RANK DESC However I also want to present the user with a list of categories and brands to filter againstonce I have this result set. So I could do something like this at the same time SELECT dbo.tbl_Product_Brands.Name AS Brand, COUNT(*) AS Products FROM dbo.tbl_Products INNER JOIN FREETEXTTABLE(dbo.tbl_Products, SearchField, 'some product') AS KEY_TBL ON dbo.tbl_Products.ProductId = KEY_TBL.[KEY] INNER JOIN dbo.tbl_Product_Brands ON dbo.tbl_Products.BrandId = dbo.tbl_Product_Brands.ID GROUP BY dbo.tbl_Product_Brands.Name ORDER BY PRoducts DESC But I seem to be executing the same dataset each time. Is there a more efficient way to do this? Should I get the initial product results into a temp table then use this to return get the brands?
What I have done for now is select the initial query PK's to a temporary memory resident table then joined this to subsequent queries so I don't hav to run a complex WHERE again and can just use PK values.
I don't understand is the query running slow? Did you try setting up indexes on columns that are frequently querried against?
I think you have missed he point. The issue is that in order to do this I'm trying to avoid running the same complex WHERE clause 3 or 4 times in order to get the initial product list then the subsequent sub lists of categories and brands etc. By having a temp table of primary keys I can relate this to subsequent quieries and even save it for paging to prevent the need for running the same WHERE over and over. It seems to work an significantly faster.
I really like using the MySQL Query Browser Tool on a locally installed database. This allows me to play around with the queries. I tried to find the tool but they've revamped it into the workbench. I actually use the workbench too for creating a visual representation of any databases I create (also an awesome tool). http://dev.mysql.com/downloads/workbench/ Shannon