StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There is little you can do with this query.</p> <p>Try this:</p> <ol> <li><p>Create a <code>PRIMARY KEY</code> on <code>categoryIds (categoryId)</code></p> <ul> <li><p>Make sure that <code>supplier (supplied_id)</code> is a <code>PRIMARY KEY</code></p></li> <li><p>Make sure that <code>category_product (ProductID, CategoryID)</code> (in this order) is a <code>PRIMARY KEY</code>, or you have an index with <code>ProductID</code> leading.</p></li> </ul></li> </ol> <p><strong>Update:</strong></p> <p>If it's <code>INSERT</code> that causes the problem and <code>product_search_query</code> in a <code>MyISAM</code> table the issue can be with <code>MyISAM</code> locking.</p> <p><code>MyISAM</code> locks the whole table if it decides to insert a row into a free block in the middle of the table which can cause the timeouts.</p> <p>Try using <code>INSERT DELAYED</code> instead:</p> <pre><code>IF @resultsFound > 0 THEN INSERT DELAYED INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId); END IF; </code></pre> <p>This will put the records into the insertion queue and return immediately. The record will be added later asynchronously.</p> <p>Note that you may lose information if the server dies after the command is issued but before the records are actually inserted.</p> <p><strong>Update:</strong></p> <p>Since your table is <code>InnoDB</code>, it may be an issue with table locking. <code>INSERT DELAYED</code> is not supported on <code>InnoDB</code>.</p> <p>Depending on the nature of the query, <code>DML</code> queries on <code>InnoDB</code> table may place gap locks which will lock the inserts.</p> <p>For instance:</p> <pre><code>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); </code></pre> <p>This query performs <code>ref</code> scans and places the locks on individual records:</p> <pre><code>-- 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 </code></pre> <p>This query, while doing the same, performs a <code>range</code> scan and places a gap lock after key value <code>2</code>, which will not let insert key value <code>3</code>:</p> <pre><code>-- 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 </code></pre>

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