On Mon 2003-03-10 at 15:06:05 -0500, [EMAIL PROTECTED] wrote:
> >
> > If you're indexing all 50 characters, index fewer of them.
>
> Not that I think you're wrong, but help me understand, please:
>
> It seems to me that this would cause the index creation to go faster,
> but the execution of the SELECT query to, if anything, go slower...?
>
> I'm probably wrong, though, but just wondered why. :)
A simple example of why it can be faster ("can", not "is") for selects
is obvious, if you have a look at caches for a moment: If you have
10.000.000 rows and an index on a char[50], you have an index of about
(50+4)*10.000.000 ~= 540MB. If you limit the index to 10 chars, you'll
need 140MB. If you have 256MB, the one may fit into memory, the other
may not and therefore requires additional disk reads.
Another example would be regarding disk reads: MySQL reads always a
whole block (1KB) from the index. With 14 bytes (plus some
adminstrative overhead) far more index entries fit into one block than
with 54 bytes. I.e. one disk read has a higher chance to fetch the
index entry you need next and make the next disk read unnecessary.
The situation is actually far more complex - I only wanted to show an
example why the seemingly slower configuration can be faster: due to
limited system resources, configurations which use available resources
wisely can be faster.
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
