Optimizing for large searches

Databases
1

I have a big database with over 250.000 descriptions.

To prevent searching a lot with querys like %‘searchword_here%’ I thougt to make a separate table with the keywords.

So I have 2 tables

  • table1, with the ids and original descriptions
    for example,
    description_id 1: description: word1,word2,word3,word4
    description_id 2: description: word1,blabla2,word3,blabla4
    description_id 3: description: blabla1,word2,word3,blabla4

  • table 2, with the separate words from the descriptoins, and ids , extracted from table 1 and separated with a |
    a short example:
    id 1: word 1 description_ids: 1|2
    id 2: word 2 description_ids: 1|3
    id 3: word 3 description_ids: 1|2|3
    id 4: word 4 description_ids: 1
    id 4: word blabla2 description_ids: 2
    id 5: word blabla1 description_ids: 3
    id 6: word blabla4 description_ids: 3
    with primary key on id, and index on the word

When searching query looks in table2 for the search words, get the description_ids and shows the complete description etc. getting it from table1

Searching now goes very fast.
But inserting new words in table 2 and/or updating the records, or starting from the beginning costs a lot of time when the table2 is getting bigger and bigger with more words and ids.

How can I make this more efficient and faster when building the table2 from the beginning?

Thanks for your help !
Valeria

2

From your description it sounds to me like it’s time to install Spinx :slight_smile:

3

After reading it, it sounds great Remon

But installing it on my dedicated server is a problem for me, because I can’t (or don’t know) how to work on the command line

Isn’t there a possiblilty to upload it to the server and run the installation programm?

Thanks,
Valeria

4

I’m afraid not; Linux doesn’t work that way. Could you maybe ask your host to install it for you? My host is always willing to do this. Albeit for a small fee, of course.
I think it would be worth it though, as I’m pretty certain those queries can never be tweaked to be as fast as with Spinx. :slight_smile:

5

Hallo Remon,

Je komt uit Nederland zie ik, dus kan je deze wel in Nederlands beantwoorden :wink:

Heb het mijn hosting gevraagd, en kreeg dit antwoord:

Op de website kunnen wij geen informatie vinden wat de eisen zijn van dit pakket, ook zijn we niet op de hoogte van deze software. Als dit dus dient te worden geïnstalleerd kan dit alleen op eigen risico, ik weet niet wat de invloed is op de rest van de aanwezige software.
Directadmin onderdelen kunnen bv onherstelbaar beschadigd worden etc, u wilt dit risico nemen?

De installatie kunnen wij dus voor u uitvoeren op eigen risico en op uur tarief van xxx ex btw, wij zullen de software enkel installeren niet configureren, dit dient zelf te doen. Ook verlenen wij verder geen support op deze software