Autocomplete locking the database

Asked

Viewed 102 times

2

I am developing an application in PHP and Jquery with the Codeigniter 3 framework. I am using Postgre and I have a database with more than 1 million registered records.

I made an autocomplete that searches by the name of a product in the bank, only as it searches in real time in the database starts to lock everything. I tested using a static array and worked 100%.

Computationally speaking as my database has many records, what would be the best way to do this?

PS: I’m using this jquery plugin (https://github.com/devbridge/jQuery-Autocomplete)

UPDATING

I am using Full Text Search, but I have not created any index for it. I will try to create it to see if it improves. See my code:

$query = $this->db->query("SELECT p.titulo AS value 
FROM sistema.produto p
WHERE to_tsvector(p.titulo) @@ to_tsquery(?)
ORDER BY p.id_categoria 
LIMIT 5", array($termo));

php jquery self-cmplete codeigniter-3

  • Andre, first not to mess with your application. if you use mysql, see about table partitioning. Create partitioning on these records and test.

1 answer

3

Postgresql Fulltext Search

If you don’t want to make significant changes to the software or infrastructure, see Postgresql Fulltext Search.

It is a function/resource of Postgresql to perform searches in some determinator fields.

Elasticsearch

If you can make more significant changes, I recommend using Elastic Search along with your Type Ahead - or Auto Complete. Now that’s all there is to it. With a separate search service, you can create all the searches you need, and take any load from your main database.

  • I liked the tip friend, but in the first option my bank is Postgresql. You know without any alternative? Hugs!

  • I edited the answer

  • Thiago my query already used Fulltext Search (I edited the post see). But I saw in the documentation that you gave me that you can create indexes to increase speed. I will try here thanks!

  • Tell me if it worked later.

  • So Thiago, see the code I’m using there in the update. Sometimes it works, but sometimes it starts to give error. I looked in the Postgre log and queries that would work normal pgAdmin give error. I think it’s because it keeps calling this function every time the user types... I don’t know :(

  • You’re hitting the barrier that we’ve all gone through: when the system grows and the resources "break branch" - like this Fulltext Search - start to fail. I believe scaling your solution is the best way. Create an individual search service, synchronize the data, and start searching in a dedicated search service. If you need a Advisor for this project, contact and we can talk.

  • Okay, how do I contact you? An alternative I thought would be to create a table with search terms. As she would be smaller I believe it would work.

  • Go to pronext.com.br and contact there, and say you need to talk to me Thiago Lunardi about software development.

Show 3 more comments

Browser other questions tagged php jquery self-cmplete codeigniter-3

You are not signed in. Login or sign up in order to post.