StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Under normal query processing, MySQL will scan the index for the where clause values (i.e. zipId = 13458). Then uses the index to look up the corresponding values from the MyISAM main table (a second disk access). When you load the table into memory, the disk accesses are all done in memory, not from reading a real disk. </p> <p>The slow part of the query is the lookup from the index into the main table. So loading the index into memory may not improve the query speed. </p> <p>One thing to try is <a href="http://dev.mysql.com/doc/refman/5.5/en/explain.html" rel="nofollow">Explain Select</a> on your queries to see how the index is being used. </p> <p><strong>Edit</strong>: Since I don't think the answers to your comments will fit in a comment space. I'll answer them here. </p> <p>MyISAM in and of itself does not have a cache. It relies upon the OS to do the disk caching. How much of your table is cached by depends upon what else you are running in the system, and how much data you are reading through. Windows in particular does not allow the user much control over what data is cached and for how long. </p> <p>The OS caches disk blocks (either 4K or 8K chunks) of the index file or the full table file.</p> <pre><code>SELECT indexed_col FROM tb1 WHERE zipId+0>1 </code></pre> <p>Queries like this where you use functions on the predicate (Where clause) can cause MySQL to do full table scans rather than using any index. As I suggested above, use EXPLAIN SELECT to see what MySQL is doing. </p> <p>If you want more control over the cache, try using an INNODB table. The <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html" rel="nofollow">InnoDB engine</a> creates its own cache which you can size, and does a better job of keeping the most recent used stuff in it. </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