How to index - fulltext? % first char? 1

[Leozack]

Hi again all. having taken advice on normalisation etc which took most of my summer, I'm now down to the final thing - indexing.

So far I have put a normal index on the name and price columns of my main table, and the parent_id (foreign key) columns of my reference tables (though I just read on another thread whislt searching for help that this doesn't help as the table is small already?)
But I have about 15-20 fields they can search for, and currently they are included in the search if they have been filled in with AND colname LIKE %input%.

However I've read previously that if you start a text search with % it means a full table scan which is bad if you get a big db, thuogh I doubt mine ever will be. This seems stupid because if you take it off, it won't find the bar in foobarfoo which isn't much help.

So - use fulltext you say. What - on all 15 or so fields? This seems ridiculous to me. I need someone to set me straight, and fast, I want to launch

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[TonyGroves]

Fulltext searching (using MATCH) only matches against individual words. For example, it won't find "foobar" in "barfoobarfoo", but it will find it in "bar foobar foo". It also uses its own discretion as to whether finds are relevant enough to be returned; it won't return matches for "foo" because the word is too short; or "the" because it's too common. It's really designed for searching big TEXT fields in big tables, though it can also be used for CHAR/VARCHAR fields in smaller tables. Web-search engines are a familiar example of fulltext searching. It only works for MyISAM tables.

Text pattern matching (using LIKE or REGEXP) is more common for CHAR/VARCHAR fields and is probably what you want. It gives more consistent results than fulltext-searching, because it simply returns all the matches it finds, instead of deciding for itself which matches to return.

 

[Leozack]

Yeah I currently have fulltext searching for %input% but if that causes a table scan every time then if the db gets big it's gonna be a problem right?

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[TonyGroves]

You don't use fulltext searching that way. An example of fulltext searching would be:[tt]
SELECT id
FROM tblname
WHERE MATCH(description) AGAINST ('input')[/tt]
This should return all records where the description field contains the word 'input', but you might not be so lucky with other words.
Since fulltext searching normally uses indexes, performance is usually reasonable.

I don't know the details of your system, but it sounds as if you might be better off sticking with LIKE ("WHERE description LIKE '%input%'"), even if the searches end up taking some time.

 

[Leozack]

Yeah sorry I completely typed fulltext instead of like. What I meant to say was I have a query built up of lots of (if they're filled in) AND colname LIKE %input%. but won't that table scan every time to isn't it a bad idea? But taking the % off the front forces the word to start with the input which it might not

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[TonyGroves]

You really need to decide which system, MATCH or LIKE, is right for you, regardless of speed, as they operate in different ways, taking different input and returning different results. Then, you have no real choice but to accept whatever performance level your chosen system operates at.

 

[Leozack]

I guess that means I need to use like then. Well fair enough I never knew the db DIDN'T scan all the rows looking for the first matched column condition then scannign those looking for the 2nd and so on. That's how you'd assume it worked. I've indexed the 2 fields I think will be searched the most. I guess if I ever top 10000 rows or something crazy I should index all fields and take a huge hit when entering/updating. Or maybe I should try to track what queries are being run so I can see which fields are searched the most.

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[TonyGroves]

I never said that "LIKE '%input%'" uses indexes; it doesn't. If you're using LIKE to find strings within fields, then you've no choice in the matter, you have to use a leading %, which prevents the use of an index. Therefore, indexing those fields will be of no benefit here. You just have to accept the performance hit.

 

[Leozack]

What so indexing only works if you don't have a % on the front too? So anything except LIKE input% or a fulltext which might not return what you want, results in a table scan? Hmm oh well, table scans it is then. I'll take my indexes out ><;

_________________________________
Leozack

MakeUniverse($infinity,1,42);