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
indexing.
> 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
beckman@purplecow.com http://www.purplecow.com/
---------------------------------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
