StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL stored procedure causing problems?
text
Body
copied!<p>EDIT:</p> <p>I've narrowed my mysql wait timeout down to this line:</p> <pre><code> IF @resultsFound > 0 THEN INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId); END IF; </code></pre> <p>Any idea why this would cause a problem? I can't work it out!</p> <p>Hi guys, I've written a stored proc to search for products in certain categories, due to certain constraints i came across, i was unable to do what i wanted (limiting, but whilst still returning the total number of rows found, with sorting, etc..)</p> <p>It's meant splits up a string of category Ids, from 1,2,3 in to a temporary table, then builds the full-text search query based on sorting options and limits, executes the query string and then selects out the total number of results.</p> <p>Now, I know I'm no MySQL guru, very far from it, i've got it working, but i keep getting time outs with product searches etc.. so I'm thinking this may be causing some kind of problem?</p> <p>Does anyone have any ideas how I can tidy this up, or even do it in a much better way that I probably don't know about?</p> <p>Thanks..</p> <pre><code>DELIMITER $$ DROP PROCEDURE IF EXISTS `product_search` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `product_search`(keywords text, categories text, topLevelCategoryId int, sortOrder int, startOffset int, itemsToReturn int) BEGIN declare foundPos tinyint unsigned; declare tmpTxt text; declare delimLen tinyint unsigned; declare element text; declare resultingNum int unsigned; drop temporary table if exists categoryIds; create temporary table categoryIds ( `CategoryId` int ) engine = memory; set tmpTxt = categories; set foundPos = instr(tmpTxt, ','); while foundPos <> 0 do set element = substring(tmpTxt, 1, foundPos-1); set tmpTxt = substring(tmpTxt, foundPos+1); set resultingNum = cast(trim(element) as unsigned); insert into categoryIds (`CategoryId`) values (resultingNum); set foundPos = instr(tmpTxt,','); end while; if tmpTxt <> '' then insert into categoryIds (`CategoryId`) values (tmpTxt); end if; CASE WHEN sortOrder = 0 THEN SET @sortString = "ProductResult_Relevance DESC"; WHEN sortOrder = 1 THEN SET @sortString = "ProductResult_Price ASC"; WHEN sortOrder = 2 THEN SET @sortString = "ProductResult_Price DESC"; WHEN sortOrder = 3 THEN SET @sortString = "ProductResult_StockStatus ASC"; END CASE; SET @theSelect = CONCAT(CONCAT(" SELECT SQL_CALC_FOUND_ROWS supplier.SupplierId as Supplier_SupplierId, supplier.Name as Supplier_Name, supplier.ImageName as Supplier_ImageName, product_result.ProductId as ProductResult_ProductId, product_result.SupplierId as ProductResult_SupplierId, product_result.Name as ProductResult_Name, product_result.Description as ProductResult_Description, product_result.ThumbnailUrl as ProductResult_ThumbnailUrl, product_result.Price as ProductResult_Price, product_result.DeliveryPrice as ProductResult_DeliveryPrice, product_result.StockStatus as ProductResult_StockStatus, product_result.TrackUrl as ProductResult_TrackUrl, product_result.LastUpdated as ProductResult_LastUpdated, MATCH(product_result.Name) AGAINST(?) AS ProductResult_Relevance FROM product_latest_state product_result JOIN supplier ON product_result.SupplierId = supplier.SupplierId JOIN category_product ON product_result.ProductId = category_product.ProductId WHERE MATCH(product_result.Name) AGAINST (?) AND category_product.CategoryId IN (select CategoryId from categoryIds) ORDER BY ", @sortString), " LIMIT ?, ?; "); set @keywords = keywords; set @startOffset = startOffset; set @itemsToReturn = itemsToReturn; PREPARE TheSelect FROM @theSelect; EXECUTE TheSelect USING @keywords, @keywords, @startOffset, @itemsToReturn; SET @resultsFound = FOUND_ROWS(); SELECT @resultsFound as 'TotalResults'; IF @resultsFound > 0 THEN INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId); END IF; END $$ DELIMITER ; </code></pre> <p>Any help is very very much appreciated!</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