Purpose of indexing? MySQL Help

[Capo64]

I'm making a search engine for my website, and I'm not very familiar with MySQL Optimization.

First question is what does indexing do? Is there anything that's bad about it? (aka Why don't i just index all of my columns).

Second question: Is there anything I can do in PHP code that will optimize my results, is there anything that is bad to do?

Right now my code:

* Splits up the query into words that are 2 characters are more and removes stopwords (me, you, what, etc..)
* for each word: mysql queries a SELECT * FROM table where description LIKE '%$word%'
* stores all results in an array with key='title' value++ (score for the word)
* for each word: mysql queries a SELECT * FROM table where title LIKE '%$word%'
* stores all results in an array with key='title' value += 5 (score for the word)
* mysql queries SELECT * FROM table WHERE title = 'searchQuery'
* stores all results in an array with key='title' value += 10
* foreach results as result it displays the title and the score

[nasty.web]

Originally Posted by Capo64

First question is what does indexing do? Is there anything that's bad about it? (aka Why don't i just index all of my columns).

Index is your table sorted by key order (the fields you've chosen) ... kinda If you create too many indexes insert and edit operations will became slow (Your table will be resorted many times on every insert). Also it's a waste disk space.

Originally Posted by Capo64

Second question: Is there anything I can do in PHP code that will optimize my results, is there anything that is bad to do?

Right now my code:

* Splits up the query into words that are 2 characters are more and removes stopwords (me, you, what, etc..)
* for each word: mysql queries a SELECT * FROM table where description LIKE '%$word%'
* stores all results in an array with key='title' value++ (score for the word)
* for each word: mysql queries a SELECT * FROM table where title LIKE '%$word%'
* stores all results in an array with key='title' value += 5 (score for the word)
* mysql queries SELECT * FROM table WHERE title = 'searchQuery'
* stores all results in an array with key='title' value += 10
* foreach results as result it displays the title and the score

You might find this helpful: http://dev.mysql.com/doc/refman/5.1/...xt-search.html

[Capo64]

Thanks for the help, I'm trying to figure out this MATCH() AGAINST() I can't seem to get it to work the right way.

I have it working OK, but it's not doing well if the person searches for a keyword with a hyphen, and the relevancy is kinda messed up.

[Capo64]

Are there illegal characters that need to be replaced in search queries?

[nasty.web]

This might be helpful:
http://dev.mysql.com/doc/refman/5.0/...ne-tuning.html
http://dev.mysql.com/doc/refman/5.0/...stopwords.html

[Capo64]

Thanks a lot so far nasty,

Can anyone tell me why this isn't a valid query? This stuff is killing me..

                            $query = "SELECT title, description, id,
                            MATCH(title,description)
                            AGAINST (
                                REPLACE('$clean', '-', ' ')
                            ) AS Relevance
                            FROM items
                            WHERE
                                MATCH (
                                    REPLACE(title, '-', ' ')
                                    ,
                                    REPLACE(description, '-', ' ')
                                )
                                AGAINST (
                                    REPLACE('$clean', '-', ' ')
                                    IN BOOLEAN MODE
                                )
                            ORDER BY Relevance DESC";

I know for a fact it's something with the REPLACE(title.... and the REPLACE(description...
because it works without the replace. I don't see what's wrong with it though..
It's not that I don't have an index because it doesn't give me that error. It gives me the error that it's invalid syntax.

[nasty.web]

MATCH(column, ... ) requires a column name as its parameter not an expression.
My quick suggestion would be to use two columns in your table for the same field: one for indexing, second for actual data.

[Capo64]

Wouldn't that be a little strenuous? My descriptions are pretty long..

Is there a different solution that I con do on the site?

It would be extremely slow to get the titles and descriptions of all my items in an array, then go through the array to replace them and redo the search, right?

[nasty.web]

Originally Posted by Capo64

Wouldn't that be a little strenuous? My descriptions are pretty long..

Is there a different solution that I con do on the site?

It would be extremely slow to get the titles and descriptions of all my items in an array, then go through the array to replace them and redo the search, right?

why do you think you need an array? I suggest you keeping two columns for the same field in a table. Eg.: title (this one for actual data) and title_idx (this one for indexing).

[Capo64]

yeah, but what about the descriptions, I'm searching through the description too

[nasty.web]

Originally Posted by Capo64

yeah, but what about the descriptions, I'm searching through the description too

Add second column for descriptions as well (I still can't see where you need an array). I believe that disk space is much cheaper than your work, so having additional columns wont hurt much (and it's the easiest and the fastest solution).

[Capo64]

Alright thanks,

So I would just add a column like title_idx and description_idx, then make a php page that removes hyphens and stop words from each description & title and adds them to the new columns?

[nasty.web]

Yes,
you can update your whole table with something like

UPDATE table SET title_idx = REPLACE(title,'-',''), description_idx = REPLACE(description,'-','')

And then on each insert or update you should do exactly the same (just add WHERE clause).

[Capo64]

Ok, Thank you so much for all your help nasty.