CODEWITHSUNDEEP
phpmysql
pawan sen
pawan sen

Reputation: 736

how to get records in a table auto-suggest input but query response very slow because 1 million records in table?

I am using mysql here is my query,i want query response fast.

I am getting records in auto-suggest input field and i have one million records in table my query response very slow.

How to can i fast query response in auto-suggest?

Upvotes: 0

Views: 40

Answers (2)

md asif rahman
md asif rahman

Reputation: 389

There are many way to optimize query:

  1. select only those columns that you need, sometimes we select all columns even if we dont need all

  2. your table structures should be normalized like escape repetition

  3. if possible remove constraints like foreign key constraints.

    for example no need to write sql to declare a field foreign key

  4. use innoDB database engine if there is nothing like transaction

Upvotes: 0

Alex
Alex

Reputation: 478

First, you might want to give us your query if you want a detailed answer. Second, there are a few things you can do :

1 - Don't start the auto completion on the first character typed by the user, if someone starts typing "e" he'll probably get way too many answers which aren't really relevant...Let's start looking for an auto-completion after 2-3 characters.

2 - As @Mark Baker suggested, indexes in your table are really important, so make sure the fields you look into are indexed.

3 - We need your query for this one, but you might want to use a lighter query by doing (maybe) different queries instead of a really heavy one.

But as i said earlier, posting your query would probably get you a lot more detailed answer.

Upvotes: 1

Related Questions