CODEWITHSUNDEEP
phpmysqlsearch
JexSrs
JexSrs

Reputation: 155

mysql - fastest searching

Well, I have created a search field for my website. When user puts his data I use them to search in database in specific columns of a table.

I am using this code:

$result = mysqli_query($conn, "SELECT * FROM table1 WHERE
column1 LIKE '%{$search}%' OR 
column2 LIKE '%{$search}%' OR
column3 LIKE '%{$search}%' OR
column4 LIKE '%{$search}%' OR
column5 LIKE '%{$search}%' OR .....");

Now when I put more and more information in database searching slows down.

I know that when I put many data in database it will slow down but is there any way to search faster?

Upvotes: 0

Views: 1331

Answers (2)

stkertix
stkertix

Reputation: 81

Try to index every column that you want to search. I think it's the first aid.

if you're using phpMyAdmin, you can open the table structure and see in the middle of page.

enter image description here

Upvotes: 0

Lucas Gomes
Lucas Gomes

Reputation: 356

You can try to use LIMIT and OFFSET.

Instead of searching through all records you can do by splitting the search. This can be achieved by pagination or infinite scroll method

Let's use pagination for example. And we will use 10 records for each page.

numOffset = numRecords(10) * (page - 1);
numLimit = numRecords(10);


SELECT * from table1 WHERE column1 LIKE '%asd%' LIMIT <numLimit> OFFSET <numOffset>;

The OFFSET defines a starting point for your query result.

The LIMIT defines how many records we want to query, in this case, 10;

If we are in page 3, it will give us an OFFSET of 20. We want to start from 20 in page 3 because the lesser records were already "queried" from the page 1 and 2. The final query will give us the records starting on 20, plus 10 records due to the LIMIT.

Upvotes: 1

Related Questions