编辑:
我把我的mysql等待超时缩小到这一行:
IF @resultsFound > 0 THEN
INSERT INTO product_search_query (QueryText,CategoryId) VALUES (keywords,topLevelCategoryId);
END IF;
知道为什么会导致问题吗?我无法解决这个问题!
嗨伙计们,我写了一个存储过程来搜索某些类别的产品,由于我遇到的某些限制,我无法做我想要的(限制,但仍然返回找到的总行数,排序等.)
这意味着将1,2,3 in的类别ID拆分为临时表,然后根据排序选项和限制构建全文搜索查询,执行查询字符串,然后选择结果总数.
现在,我知道我不是MysqL大师,离它很远,我已经有了它的工作,但我一直在等待产品搜索等等.所以我认为这可能会导致某种问题?
有没有人有任何想法如何我可以整理它,甚至以一种我可能不知道的更好的方式做到这一点?
谢谢..
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 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,topLevelCategoryId);
END IF;
END $$
DELIMITER ;
非常感谢任何帮助!