Re: Text Searches on MySQL

First of all, to answer the inital question, here's a quote from the database: * It's not normally useful to split a table into different tables just because the rows gets 'big'. To access a row, the biggest performance hit is the disk seek to find the first byte of the row. After finding the data most new disks can read the whole row fast enough for most applications. The only cases where it really matters to split up a table is if it's a dynamic row size table (see above) that you can change to a fixed row size, or if you very often need to scan the table and don't need most of the columns. So basically, unless all the other fields in the table are constant length (CHAR) fields, you shouldn't bother with splitting the table. As for fulltext indexing, MySQL has supported this for a while in 3.23. It's not far beyond the experimental stage, but it's pretty bug free and is much easier to implement than the solution described below. Search for FULLTEXT in the MySQL manual. Jon Valvatne On Wed, 14 Mar 2001, Ron Brogden wrote: > At 05:05 PM 3/14/2001 +0000, you wrote: > >I'm putting together a db with articles in it. Would it be better to have two > >tables with the main text of the articles in it linked to a main article > >table or just chuck it all in together in one table. > >I want to be able to do a full text search on the articles. > > You cannot index "TEXT" fields in MySQL so doing a full text search is slow > if you have any amount of data in your database (it needs to search every > record for every search). The way I normally handle this is to create a > second table of "words" which includes every word in a given article (that > isn't in a stop word list) along with the original article ID so that I can > index the strings. This makes a search very quick but adds the expense of > disk space and slows the adding of data initially. You will have to decide > whether in your case it matters. If you have little data then no big > whoop. If you have lots of data then you need to look at different method > of searching. > > Cheers, > > Ron > > ----------------------------------------------------------------------------- > Island Net AMT Solutions Group Inc. Telephone: 250 383-0096 > 1412 Quadra Toll Free: 1 800 331-3055 > Victoria, B.C. Fax: 250 383-6698 > V8W 2L1 E-Mail: support@islandnet.com > Canada WWW: > http://www.islandnet.com/ > ----------------------------------------------------------------------------- > >