Skip to content
Advertisement

i have a problem with search using php, mysql

I have 50M data in my database (mysql phpmyadmin), when I search inside this data using php it takes a lot of time, very slow

How can i solve this problem?

my code :

$mysql_search = "SELECT * FROM data WHERE columns='value'";
$search_query = mysqli_query($conn, $mysql_search);

foreach($search_query as $row){
     echo $row['id'];
}

and thanks

I’ve already tried using limit and Like But nothing happened

$mysql_search = "SELECT * FROM data WHERE columns LIKE 'value%' LIMIT 5";

Advertisement

Answer

There are three things I can suggest you that can improve the performance of your search:

Use an index: Adding an index on the column can significantly improve the performance of your search, especially if you have a large table.In that case on your column field where you’re trying to do that search. For example:

CREATE INDEX index_name_column ON data (column);

Optimize your query: Make sure that your query is optimized for performance. For example, try to fetch only required fields while searching like

$mysql_search = "SELECT filed1, field2 FROM data WHERE columns LIKE 'value%' LIMIT 5";

Use LIMIT and OFFSET: Select rows that you need only while doing the search. For example: you can combine LIMIT and OFFSET to narrow down your search results like-

$mysql_search = "SELECT field1, field2 FROM data WHERE columns LIKE 'value%' LIMIT 5 OFFSET 10";
5 People found this is helpful
Advertisement