Problems with the MySQL stored procedure?

Question

EDIT:

I narrowed the mysql wait timeout to this line:

IF @resultsFound > 0 THEN INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId); END IF; 

Any idea why this might cause the problem? I can’t solve it!

Hi guys, I wrote a saved proc to search for products in certain categories, due to certain restrictions that I encountered, I could not do what I wanted (limiting, but at the same time returning the total number of rows found, with sorting, etc. .d.)

This meant splitting the row of category identifiers from 1,2,3 to the temporary table, and then building a full-text search query based on the sorting parameters and restrictions, executing the query string, and then selecting the total number of results.

Now, I know that I'm not a MySQL guru, very far from him, it works for me, but I keep getting timeouts with searching for products, etc., so I think this may cause some problem?

Does anyone have any ideas how I can remove this, or even do it much better than I probably don't know?

Thanks..

 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 ; 

Any help is much appreciated!

Answer 1

There is little you can do with this request.

Try the following:

• Create a PRIMARY KEY on categoryIds (categoryId)

  • Verify that the supplier (supplied_id) is a PRIMARY KEY

  • Make sure category_product (ProductID, CategoryID) (in that order) is a PRIMARY KEY , or you have an index with ProductID .

Update:

If it causes an INSERT problem and product_search_query in the MyISAM table, the problem may be with MyISAM locking.

MyISAM blocks the entire table if it decides to insert a row into a free block in the middle of the table, which can cause timeouts.

Try using INSERT DELAYED instead:

 IF @resultsFound > 0 THEN INSERT DELAYED INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId); END IF; 

This will put the entries in the insert queue and return immediately. The record will be added asynchronously later.

Please note that you may lose information if the server dies after issuing a command, but before the records are actually inserted.

Update:

Since your table is InnoDB , this can be a problem when locking the table. INSERT DELAYED not supported on InnoDB .

Depending on the nature of the DML request, queries in the InnoDB table may place lock locks that will block insertions.

For example:

 CREATE TABLE t_lock (id INT NOT NULL PRIMARY KEY, val INT NOT NULL) ENGINE=InnoDB; INSERT INTO t_lock VALUES (1, 1), (2, 2); 

This request performs a ref scan and places the locks in separate records:

 -- Session 1 START TRANSACTION; UPDATE t_lock SET val = 3 WHERE id IN (1, 2) -- Session 2 START TRANSACTION; INSERT INTO t_lock VALUES (3, 3) -- Success 

This query, doing the same thing, performs a range check and places a gap lock after the key 2 value, which will prevent the key 3 value from being inserted:

 -- Session 1 START TRANSACTION; UPDATE t_lock SET val = 3 WHERE id BETWEEN 1 AND 2 -- Session 2 START TRANSACTION; INSERT INTO t_lock VALUES (3, 3) -- Locks 

Answer 2

Turn on slow queries that will give you an idea of ​​what takes so long to complete, that there is a timeout.

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Select the slowest query and optimize it. then run for a while and retry.

Great info and tools here http://hackmysql.com/nontech

DC

UPDATE:

Or you have a network problem causing a timeout, if you use a local mysql instance, then this is unlikely, OR something is blocking the table for too long, causing a timeout. a process that locks a table or tables for too long will be listed in the slow log as a slow query. you can also get a slow log query to display any queries that cannot use the index, resulting in an inefficient query.

If you can solve the problem during your presence, you can also use a tool such as phpmyadmin or the command line to run "SHOW PROCESSLIST \ G", this will give you a list of queries that are executed while the problem is occurring.

You think the problem is with your insert statement, so something is blocking this table. so you need to find what locks this table, so you need to find what works so slowly that it locks the table for too long. Slow queries are one way to do this.

Other things to watch

CPU - it is idle or running at full speed

IO - causes retention

RAM - you swap all the time (will cause excessive io)

Does table use product_search_query index?

What is the primary key?

Should your index use too long rows? you can create a huge index file that causes very slow inserts (the slow query log will also show this)

And yes, the problem may be elsewhere, but you must start somewhere wrong.

DC

Answer 3

Try wrapping your EXECUTE as follows:

SETS THE INSULATION LEVEL OF AN INTEGRATED SESSION IS ILLEGAL,

EXECUTE USE CHOICE @keywords, @keywords, @startOffset, @itemsToReturn;

SETTING BET MEETINGS INSULATION LEVEL RE-READING;

I am doing something similar in TSQL for all reports stored in proc and searches where repeated reads are not important to reduce lock / lock issues with other processes running in the database.