2

Ok so I can probably see WHY I am getting many results, since I use so MATCH queries. What I want is for relevant searches to be outputted. For example if the search is "interest in green it" it only outputs those results from teh table that has interest or green or IT in there if that makes sense.

Right now I get resutls for everything, additionally its really rather slow. I dont want to use anything other than mysql/php to achieve this, so no third party search engines like Lucene or alternatives.

Here's the search code:

    <?php

mysql_connect("webhost", "user", "pass") or die(mysql_error());
mysql_select_db("replies") or die(mysql_error());

// Retrieve result using term
$term = $_POST['term'];

$sql = mysql_query("SELECT * from 'replies' `where interest_in_green_it` MATCH '%$term%' or `green_it_good_thing MATCH` '%$term%' or    `green_it_save_environment` MATCH '%    $term%' or  `green_it_save_money` MATCH '%$term%' or    `green_it_incentive` MATCH '%$term%' or `uel_green_it MATCH` '%$term%' or   `MATCH_green_it` MATCH '%$term%' or     `printing_costs` MATCH '%$term%' or `travel_costs` MATCH '%$term%' or   `comments` MATCH '%$term%' or   `uel_green_modules` MATCH '%$term%' or  `year_of_study` MATCH '%    $term%' or      `questionnaire_filled` MATCH '%$term%'");

while ($row = mysql_fetch_array){
echo 'ID: '.$row['ID'];
echo '<br/> Do you have an interest in green IT?: '.$row['interest_in_green_it'];
echo '<br/> Do you think green IT is a good thing?: '.$row['green_it_good_thing'];
echo '<br/> Would you consider green IT if it meant saving the environment?: '.$row['green_it_save_environment'];
echo '<br/> Would you consider green IT if it meant saving money?: '.$row['green_it_save_money'];
echo '<br/> What would be the better incentive to practice green IT?: '.$row['green_it_incentive'];
echo '<br/> DO you think UEL is doing enough to practice green IT? : '.$row['uel_green_it'];
echo '<br/> Do you like green IT?: '.$row['like_green_it'];
echo '<br/> Your estimated monthly travel costs to UEL: '.$row['travel_costs'];
echo '<br/> Your estimated printing costs at UEL at any one time: '.$row['printing_costs'];
echo '<br/> Your comments: '.$row['comments'];
echo '<br/> Would you like to see more green modules at UEL?: '.$row['uel_green_modules'];
echo '<br/> What is your year of study?: '.$row['year_of_study'];
echo '<br/> If you did not fill in the questionnaire why not?: '.$row['questionnaire_filled'];
echo '<br/><br/>';
}

$_GET = array_map('trim', $_GET); 
$_POST = array_map('trim', $_POST); 
$_COOKIE = array_map('trim', $_COOKIE); 
$_REQUEST = array_map('trim', $_REQUEST); 
if(get_magic_quotes_gpc()): 
$_GET = array_map('stripslashes', $_GET); 
$_POST = array_map('stripslashes', $_POST); 
$_COOKIE = array_map('stripslashes', $_COOKIE); 
$_REQUEST = array_map('stripslashes', $_REQUEST); 
endif; 
$_GET = array_map('mysql_real_escape_string', $_GET); 
$_POST = array_map('mysql_real_escape_string', $_POST); 
$_COOKIE = array_map('mysql_real_escape_string', $_COOKIE); 
$_REQUEST = array_map('mysql_real_escape_string', $_REQUEST); 

?>

thank you in advance

Junaid Hussain
  • 45
  • 1
  • 10

1 Answers1

2

"How can I improve this query?"

Use an index. A FULLTEXT index might be userful here.

I believe the latest InnoDB Engine supports FULLTEXT indexes, as well. (MySQL 5.6+)

To not get all possible results, calculate a score that determines how well a tuple matches your condition and ORDER BY score DESC LIMIT 20, to get the best 20 matches. See this answer for an example.

Community
  • 1
  • 1
Basti
  • 3,998
  • 1
  • 18
  • 21
  • Where did you hear that, that's awesome news if it's true, any links? – BenOfTheNorth Apr 10 '12 at 11:53
  • 1
    This was done by Oracle after they bought Sun and therefore MySQL. http://blogs.innodb.com/wp/2011/12/innodb-full-text-search-in-mysql-5-6-4/ – Basti Apr 10 '12 at 11:56
  • 1
    Thanks! (Also, apologies for the hijack of these comments!) – BenOfTheNorth Apr 10 '12 at 11:57
  • thank you for the reply, i'm just wondering how exactly the index would look like and where it would fit in the above code? I am using myISAM engine. thank you – Junaid Hussain Apr 10 '12 at 12:07
  • The index needs to be created beforehand. Create it after creating the table. The index is a persistent shortcut to your results. See the [MySQL manual](https://dev.mysql.com/doc/refman/5.6/en/create-index.html) on how to create indexes. I would suggest to use a database administration tool like [phpMyAdmin](http://phpmyadmin.net) to manipulate your table's structur. To create a fulltext index use `ALTER TABLE `myTable` ADD FULLTEXT (`myColumn1`, `myColumn2`, ...)`. The index will only work if you use all columns or any prefix of those columns. This is explained on the manual page, as well. – Basti Apr 10 '12 at 12:13