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
1
2
4
5
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);
$i=1;
foreach($pieces as $item){
$query = '%'.$item.'%';
$sql->bindParam($i++, $query, PDO::PARAM_STR);
}
$sql->execute();
$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
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.
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);
}
$sql->execute();
$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.
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.
