Actually you are not indexing on a text or blob field solely if you are
indexing a fixed length 20 char or said field. Building key fields to
index on is a good approach I often when I would like to index on a text
or blob, I instead index on a unique hashing routine instead.
As far as the later about scaling. It also does not scale well to have
to wait 24/48 hours for an index to build does not scale well. Another
solution is rather than building the index on your current database
build it on a new instance sync the data and swap databases.
**Warning optimal development process **(The process I was speaking of
is one that should scale since you should not be building index's on
your production system. You should be only building new index's during
your development. If you are building index's on your production system
somebody did not do there job and it should be put into the next serious
development instead. ) I understand it does not always work this way.
-----Original Message-----
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 11, 2001 3:01 AM
To: Wyly Wade
Cc: Joseph Bueno; Roger Ramirez; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: Re: indexing
On Tue, Jul 10, 2001 at 07:36:25PM -0400, Wyly Wade wrote:
>
> Never Index on text, blob, and if you can help it don't use var char
> in indexes.
There's nothing wrong with indexing a text or blob field. In fact,
it's often very useful to index just a small prefix (maybe 20
characters). It gives you a performance boost on some queries without
the overhead of building a massive index structure.
And varchar indexes are actually pretty cheap.
> Also it is always best to create all the all the indexes that you
> think you might need on a table before you put the data in. It only
> takes a few seconds to drop an index if you realize you do not need
> it afterward.
I don't think that advice scales well at all. But if scaling isn't
important in this case, it's a reasonable approach.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 439-9951
MySQL 3.23.29: up 24 days, processed 187,518,198 queries (87/sec. avg)
---------------------------------------------------------------------
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
