StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>1)</strong> If it's not already done, define an index on the <code>collect.id</code> column :</p> <pre><code>CREATE INDEX idx_collect_id ON collect (id); </code></pre> <p>Or possibly a unique index if you can (if <code>id</code> is never the same for any two lines) :</p> <pre><code>CREATE UNIQUE INDEX idx_collect_id ON collect (id); </code></pre> <p>Maybe you need an index on <code>collect_log.tid</code> or <code>collect_log.bid</code>, too. Or even on both columns, like so :</p> <pre><code>CREATE INDEX idx_collect_log_tidbid ON collect (tid, bid); </code></pre> <p>Make it <code>UNIQUE</code> if it makes sense, that is, if no two lines have the same values for the (tid, bid) couple in the table. For instance if these queries give the same result, it <em>might</em> be possible :</p> <pre><code>SELECT count(*) FROM collect_log; SELECT count(DISTINCT tid, bid) FROM collect_log; </code></pre> <p>But don't make it UNIQUE if you're unsure what it means.</p> <p><strong>2)</strong> Verify the types of the columns <code>collect.type</code>, <code>collect.status</code> and <code>collect_log.bid</code>. In your query, you are comparing them with strings, but maybe they are defined as <code>INT</code> (or <code>SMALLINT</code> or <code>TINYINT</code>...) ? In this case I advise you to drop the quotes around the numbers, because string comparisons are painfully slow compared to integer comparisons.</p> <pre><code>select t1.id,t1.type from collect t1 where t1.type=1 and t1.status=1 and t1.total>(t1.totalComplate+t1.totalIng) and id not in( select tid from collect_log t2 where t2.tid=t1.id and t2.bid=1146) order by t1.id asc limit 1; </code></pre> <p><strong>3)</strong> If that still doesn't help, just add <a href="http://dev.mysql.com/doc/refman/5.0/en/explain.html" rel="nofollow">EXPLAIN</a> in front of your query, and you'll get the execution plan. Paste the results here and we can help you make some sense out of it. Actually, I would advise you to do this step <em>before</em> creating any new index.</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