Resolved Database handles query very slow

[BloggerTalks]

Good morning Plesk Support, my website has 1068790 rows and it very slow to handle queries


That how i created the table and i was using it on cPanel before and worked fine, but when I moved to Plesk it seems it slow to handle users query and i make use of NGINX and Apache.. the area you see int(11) is for id, varchar(255) is for users input but well formatted to avoid hacks

Here my code i used in inserting clean users input

function filter_spam($string){ $url = str_replace("'", ' ', $string); $url = str_replace('', ' ', $url); $url = preg_replace('~[^\\pL0-9_]+~u', ' ', $url); $url = trim($url, ""); return $url;} function cleanURL($string){ $url = str_replace("'", ' ', $string); $url = str_replace("'", '', $string); $url = str_replace('%20', ' ', $url); $url = preg_replace('~[^\\pL0-9_]+~u', '-', $url); $url = trim($url, "-"); $url = strtolower($url); return $url;}

$search_term = preg_replace('/drop table|mysql|md5|sql|setcookie|db.Execute|hex2bin|public html|delete from|insert into|update table|select from/', '', filter_spam(strtolower(strip_tags($_GET['q'])))); but i don't know why Plesk is not handling the queries real fast .

Please, am i missing any settings that could help in improving the speed please? Thanks awaiting your reply

 

[Bitpalast]

All this has nothing to do with Plesk, but with your programming and the general server speed. The function given is not addressing the database, but it is replacing strings. And it is programmed in an extremely "expensive" way in terms of cpu load. It is possible that you had a faster server previously so that these PHP functions were executed faster. But none of these are accessing the database or disk, so your issue is purely a cpu load issue with your PHP programming.

For one, where possible, try to avoid all regular expression replacement functions. The fastest replacement function is str_replace, but even that should be limited to a minimum. The more values need actually be replaced, the slower these functions become, so their speed varies depending on the content that they have to process. The longer the input strings are, the slower they become, so their speed also varies depending on the length of input.

Further, a much better use of str_replace is to put all the replacements into arrays, for example
$url = str_replace(array("'", '', '%20'), array(' ', ' ', ' '), $string);
or (as the replacement value is all the same for the three inputs
$url = str_replace(array("'", '', '%20'), ' ', $string);

Then I notice that you are actually passing a copy of the $string variable into your functions. This is a waste of cpu time, because it creates a replica of the variable and content each time the function is called. Instead, you could pass a pointer to the existing variable by preceding $string with an ampersand like
function filter_spam(&$string) { ... }

 

[BloggerTalks]

Mehn you're a genius ,you can't believe i never knew about such could make my speed slow mehn no wonder my competitors search speed are faster but my page are faster cause i wrote the code well and less work but this other one that accepting users input i just had to do something that really hard to add bad words to my database or funny characters i will work on that now and it will be super b.. Thanks a lot @Peter Debik you just inspired my project thanks