Worth indexing every column?

[kaisellgren]

Hi,

Maybe a stupid questions for you guys, but I'm not a database expert, just PHP security expert :)

The only query I run on my site is:

SELECT * FROM table WHERE blah blah

I have replaced the * with the column names, but in reality it shouldn't matter, because I need to fetch every column information. Even the ID must be fetched... so my question is, is it worth adding INDEX(id), INDEX(name), etc for each column? The where clause uses only name column to match for... so perhaps I just INDEX the name column then?

Also... does anyone know how to use % -sign in FULLTEXT's AGAINST() function? Like MATCH(name) AGAINST ('%something%') ?

Thank you a lot for your help!

[guelphdad]

Indexes speed up searches, they slow down inserts and updates so it is a trade off. Your blah, blah would be the columns you are searching on with specific criteria so you need to have indexes on those columns.

As for the full text question you don't need to use % that is used for LIKE matches only.

[kaisellgren]

Indexes speed up searches, they slow down inserts and updates so it is a trade off. Your blah, blah would be the columns you are searching on with specific criteria so you need to have indexes on those columns.

As for the full text question you don't need to use % that is used for LIKE matches only.

I tried FULLTEXT. I tried to AGAINST('test') and it didnt match abctestdef :(

[brazenskies]

and you got results for that using LIKE???

EDIT: Sorry, I completely misread what you said there!!

Why not use MATCH and OR LIKE

[kaisellgren]

and you got results for that using LIKE???

EDIT: Sorry, I completely misread what you said there!!

Why not use MATCH and OR LIKE

I'm using MATCH (FULLTEXT), but I can't find any way to it to match like

aaatestaaa
bbbtestbbb
ccctesccc

AGAINST ('%test%')

results: aaatestaaa & bbbtestbbb

I can only match for whole words with it :/