title: MySQL Search page number from query
body: <p>This is probably a common question but I am not even sure if I did the right search.</p>
  
  <p>I have a pretty big query that returns a lot of data from differents tables in a certain order and filtred by some clauses. I put this in a table loading 100 rows per page. For this I use something like this:</p>
  
  <pre><code>SELECT lot_of_data_columns 
  FROM many_tables 
  WHERE many_clauses 
  ORDER BY some_column DESC 
  LIMIT (page_number * 100), 100 
  </code></pre>
  
  <p>Now I want to implement a search field to look up for a value in a certain column. If this value exist, it will load the page that contains that row. I only want the page of the first apparition of the value.</p>
  
  <p>I was thinking in something like this to get the row number in the order that is shown:</p>
  
  <pre><code>SELECT row 
  FROM (    BIG_QUERY, 
            @curRow := @curRow + 1 AS row 
            FROM table, (SELECT @curRow := 0) r
       ) t
  WHERE MATCH (column)
  AGAINST ('value' IN BOOLEAN MODE)
  LIMIT 1
  </code></pre>
  
  <p>It works, but only with some columns and rows, in others it just dont return anything even if the value exist.</p>
  
  <p>Any suggestions? Maybe i'm getting a little ravel with my querys. Is there a easier solution?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>The problem seems to be some searched values. I have some values like this:</p>
  
  <pre><code>203-21
  201-434
  343-23
  </code></pre>
  
  <p>The <strong>MATCH</strong> ignores special chars, like the '-', and ignores too what comes next, so the word lenght is less than 4, that is the minimum word lenght for the <strong>MATCH</strong>. The solution is change the <strong>ft_min_word_len</strong> value in the <strong>my.conf</strong> file. Or removing the special chars from the searched value with:</p>
  
  <pre><code>REPLACE(column, '-', '_')
  </code></pre>
  
  <p>I still would like an easier solution to all this. If someone have a better idea would be great.</p>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>Instead of <strong>MATCH - AGAINST</strong> I changed to <strong>REGEXP</strong>. Something like this:</p>
  
  <pre><code>SELECT row 
  FROM (    BIG_QUERY, 
            @curRow := @curRow + 1 AS row 
            FROM table, (SELECT @curRow := 0) r
       ) t
  WHERE (column REGEXP '.*value.*')=1
  LIMIT 1
  </code></pre>
  
  <p>And it's already working fine. But I'm a little worried about perfomance when increase the number of rows. I'll select the answer of someone who knows how to optimise my query.</p>