List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 2 1999 11:46am
Subject:Re: Create Tabe Syntax
View as plain text  
At 4:51 AM -0500 9/2/99, Jason wrote:
>Thanks Tim and Dave for your prompt answers. I know next to nothing about
>indices, does it show? :)
>
>>From what I gathered in the MySQL & mSQL book from O'Reilly:
>
>pg. 101 "Because of the structure of the index MySQL uses, any subset of the
>columns from left to right are automatically created as indices within the
>"main" index.
>
>I took this to mean I could avoid building each index separately and
>couldn't figure out why I kept getting that error.

It does mean you can avoid building each index separately, but there
are two implications in your case:

- Iou have to index just a prefix of each column in order to avoid
hitting the 256 character limit.  For example, to index just the first
20 characters of each column in the 4-column index, you could say
this: index index1 (link_id(20),url(20),title(20),content(20))

- If what you really want is having your table indexed in such a way
that indexes for any of the four columns can be used independently,
then you do need independent indexes.  The "subset of the columns
from left to right thing" means that if you have an index on
link_id, url, title, and content, it's effectively as if you also
have indexes on:

link_id, url, title
link_id, url
link_id

However, that means you do not have an index on the url, title, or
content fields that can be used if you select on just those fields.

--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/
Thread
newbie questionCarolyn Campbell2 Sep
  • Re: newbie questionThimble Smith2 Sep
  • Re: newbie questionJeff Urban2 Sep
  • Re: newbie questionSteve Ruby2 Sep
    • RE: newbie questionCarolyn Campbell2 Sep
      • Create Tabe SyntaxJason2 Sep
        • Re: Create Tabe SyntaxThimble Smith2 Sep
      • Re: Create Tabe SyntaxJason2 Sep
        • Re: Create Tabe SyntaxPaul DuBois2 Sep
Re: Create Tabe SyntaxDave Kaufman2 Sep
Re: Create Tabe SyntaxDave Kaufman2 Sep