0

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?

JexSrs
  • 155
  • 6
  • 18
  • 1
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Aug 21 '18 at 14:02
  • 1
    All of the columns you need to search need to be indexed. It will speed up searches, but slow down inserts and updates. – Jay Blanchard Aug 21 '18 at 14:03
  • And how I will do that? How to index the columns? – JexSrs Aug 21 '18 at 14:04
  • 2
    However, indexes will not help with left-wildcard searches. See https://stackoverflow.com/questions/5905125/mysql-improve-search-performance-with-wildcards – aynber Aug 21 '18 at 14:05
  • 3
    "However, indexes will not help with left-wildcard searches" indeed.@aynber MySQL supports FULLTEXT indexing that should also work on VARCHAR and CHAR columns then you can use something like `... WHERE MATCH (column1, column2, ...) AGAINST ('+{$search}' IN BOOLEAN MODE)` instead which gives the same results `... WHERE column1 LIKE '%{$search}%' OR column2 LIKE '%{$search}%' ...` does but can use the index. – Raymond Nijland Aug 21 '18 at 14:26
  • Thanks everyone for your solution but, I am confused, which is the best way? – JexSrs Aug 21 '18 at 14:31

2 Answers2

1

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.

Lucas Gomes
  • 316
  • 5
  • 16
0

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

Matthias
  • 4,481
  • 12
  • 45
  • 84
stkertix
  • 81
  • 1
  • 10