SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimizing for large searches

    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. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    From your description it sounds to me like it's time to install Spinx
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    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.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hallo Remon,

    Je komt uit Nederland zie ik, dus kan je deze wel in Nederlands beantwoorden ;-)

    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •