i have a problem with search using php, mysql-CodePudding

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";

CodePudding user response：

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";

CodePudding user response：

There are a few things you can try to improve the performance of your search query:

1- Index the columns that you are searching on. Indexing can significantly speed up search queries by allowing the database to quickly find the rows that match the search criteria.

2- Use a LIMIT clause to limit the number of rows that are returned by the query. This can help reduce the amount of time it takes to execute the query and transfer the data from the database to your PHP script.

3- Consider using a full-text search index, which can help speed up searches on large datasets by allowing you to search for specific words or phrases within a column.

4- If possible, try to narrow down the search criteria to only return the rows that you really need. The more rows that are returned by the query, the longer it will take to execute.

5- If the performance of your search query is still not sufficient, you may need to consider using a database that is optimized for large datasets and fast searching, such as Elasticsearch or Apache Solr.