On Sun, 10 Nov 2002, Chris Payne wrote:

> Hi there everyone,
> How does indexing work, and does it speed up small / average size DB's of
> around 20,000 records, with 12 columns per record?  (Some columns being
> paragraphs of text).

 YES!  20,000 records is still a lot of records, and indexing on
 commonly/frequently searched, non-primary-key fields (like email address,
 last name, or zip code for example) will definitely help speed things up.
 ALSO, making sure fulltext indexing is on your text fields if you are
 searching your paragraphs of text will significantly help the queries.
 When you query your fulltext columns (anything of type TEXT), use this:

 select match(column1) against ('myquerytext') as blah,column1,column2,column3 from 
table order by blah desc

 This will return something like this:

 2.393888483    | column1 contents  | column2 contents  | column3 contents
 1.938323487    | column1 contents  | column2 contents  | column3 contents

 if they match 'myquerytext'.  Search mysql.com documentation for fulltext

> Also, do you create an index on everything or just 1 item or or or :-)

 Depends.  If you search on a single column almost always, then make an
 index on that one column.  If you do "select * from table where col1='x'
 and col2='y'" then you'll want to create an index that includes col1 and
 col2.  Again, read the manual on mysql.com on indexing.

> I've not looked at indexing but think it's time to start, but in a way even
> I can understand so I thought i'd ask you all here :-)

Peter "RTFM"

Peter Beckman            Systems Engineer, Fairfax Cable Access Corporation
[EMAIL PROTECTED]                             http://www.purplecow.com/

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to