2 Replies Latest reply on Sep 7, 2008 12:14 PM by AngryCloud

    MySQL: Occurrences in search query

    AngryCloud Level 1
      In this example search query, how would I order the submissions by the number of occurrences?

      For example, a submission that includes 'red' twice and 'dragon' once would come before a submission that only includes each word once.

        • 1. Re: MySQL: Occurrences in search query
          Level 7
          .oO(AngryCloud)

          >In this example search query, how would I order the submissions by the number
          >of occurrences?

          I don't think this can be done with pure SQL. The usual way is to use a
          search index, which contains all found terms, the documents they appear
          in and some statistical values. This then allows to rank the terms and
          documents by frequency and relevance (for example a term found in every
          document would have no relevance at all).

          You could also have a look at MySQL's built-in fulltext search
          capabilities. I've never used it, but it might be worth a look.

          Micha
          • 2. Re: MySQL: Occurrences in search query
            AngryCloud Level 1
            This website explains an interesting way of finding how many times a keyword occurs using just MySQL:

            http://r937.com/keyword_relevance.html

            This site explains how to get a relevence factor in several different ways, but the only one I am interested in right now is the one labeled "occ":

            ( length(Description)
            - length(replace(Description,'games','')) )
            / length('games') as occ

            The problem is that the site only explains how to find the occurennces of a single keyword, rather than multiple keywords.

            I am not sure if an array in the replace function would be possible, but otherwise I was thinking I could just use PHP to repeat that code in the SELECT clause for each keyword, similar to how the keywords are repeated in the WHERE clause.

            Let me know if you have a more efficient idea.