Need better search query

I need better search query as our records keep getting larger by the day and
its already 20 GB of data

table question records
record number | title
1 nice one tanga bro
2 nice one bro
3 asd asd asd
4 asd asd asd nice asd asd bro
5 asd asd nice no no no

if i used this keyword “nice bro” it should give me a result of

record number

and i used this code

$q = 'nice bro';

$pieces = explode(" ", $q);        
        $advance_query = '';
        foreach($pieces as $v){
            $advance_query .= 'title like ? or ';
        $advance_query = substr($advance_query,0,-3);
        $sql = "select id, title, more from questions where ".$advance_query;
        $sql = $this->db->prepare($sql);
        foreach($pieces as $item){
          $query = '%'.$item.'%';    
          $sql->bindParam($i++, $query, PDO::PARAM_STR);          
    $row = $sql->fetchAll(PDO::FETCH_ASSOC);

It’s working but its kinda lame and slow and my boss wants me to improve the codes
can you help me guys to do advance search query?
please provide code for a better query
any help is highly appreciated

Make sure you have indexes on all the columns that you’re including in your WHERE clause, which looks like it’s just title.

Other than that, I don’t think there is much you can do.

It has index and the option is “unique”.
should i make it into “fulltext” ?

is there any other way to make an advance query ?

Try fulltext, that should help alot.

But if you already have indexes setup, with 20GB of data I’m not sure what you can do to speed it up short of building a custom search engine or rethinking how you have it setup. Maybe someone else has a better idea, but doing a LIKE on that much data is no trivial task.

I’m interested in how much doing a fulltext index helps out tho, so let us know when you change it. :smile:

its the same thing.
i really need to use the LIKE for the user to get a result of the keyword they entered.

im hoping someone can change or rewrite my code to make it better.
anyone? experts pls share your knowledge.

Try this, I can’t guarantee this won’t have errors. I didn’t test it because I don’t a PHP environment setup and I’m not a PHP dev, but the idea should get you going:

$pieces = explode(" ", $q);        
$advance_query = '';

$sql = "select id, title, more from questions where ";

foreach($pieces as $k=>$v){
    $advance_query=($k > 0) ? $sql.'title like ? union all ' : $sql.'title like ? ';

$sql = $this->db->prepare($sql);

foreach($pieces as $k=>$item){
  $query = '%'.$item.'%';    
  $sql->bindParam($k+1, $query, PDO::PARAM_STR);          

$row = $sql->fetchAll(PDO::FETCH_ASSOC);

If this doesn’t help, I’m out of ideas. Refer back to my custom search engine statement above. The theory behind this is that UNIONs are faster than ORs. This might get double results if you have the same word twice in the original $q query string. You’ll have to handle that in the result.

Also, refer to this to help better figure out the bottlenecks and performance differences.

i tried your suggestion but its not working.
can you give me the working code instead to make it work.

the “union all” gives me an error.
your help is highly appreciated.

As I said, I am not a PHP dev and I have no environments setup with PHP connected to a db to test this.

What is the error? Can you not fix it? My code was meant to help you, not to do your work for you.

You probably need to use double quotes instead of single or something from the looks of it. This is an error you should have been able to change.

I understand. I think it would be better if we simplify the query you mentioned.

select id, title, more from questions where title like ‘nice bro’ union all

i tested it and its not working or did i missed something?

any help is highly appreciated

Union All combines 2 select queries into 1. It doesn’t do anything but throw an error with 1.

You are not querying “nice bro” you are querying “nice” and “bro” individually, which is the point of all your code instead of just querying the input string. You were doing it with an OR which is slower than 2 individual queries concatenated with Union All.

You also need to ensure you include the % symbols in the like value (e.g. ‘%nice bro%’, other words it’s going to do a simple match