Solved

Ajax - Auto suggest drop down box. How to reduce Database overheads?

Posted on 2008-10-17
15
3,551 Views
Last Modified: 2013-12-17
Hi everyone,

Right now, we are developing a system with which the results are displayed real time via AJAX. What happens is that as user is keying  in a value in the textbox, the results are displayed accordingly and is changed immediately based on what is keyed-in.

This in turn will cause a huge database overhead in a heavy traffic enviroment.

Most of the queries are

select col1, col2 from table1 where col3 like '%something%'

Are there any better ways to implement this?

Please advise.

Thanks
0
Comment
Question by:stevong
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
15 Comments
 

Author Comment

by:stevong
ID: 22738670
Anyway is to use fulltext search.

But it's still a lot of db request and overheads :(
0
 
LVL 6

Expert Comment

by:openshac
ID: 22738692
>>This in turn will cause a huge database overhead in a heavy traffic enviroment.

Not really you're only send a few records (probably no more than 10) at a time.
Yes there are many more trips to the database but the "TRAFFIC" is by no mean "heavy".
0
 

Author Comment

by:stevong
ID: 22738739
So far most of the the systems I've implemented are using some kind of caching mecahnism. Server will fetch from plain text files instead of fetching from DB everytime. Cache will only be rebuilt based on the timestamp when the article is generated. Database request is kept to the utmost minimum.

But when it comes to the auto suggest fanciful features, I'm a little bit worried of the database load.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 6

Expert Comment

by:openshac
ID: 22738777
What is the difference in traffic between returning the full resultset and a set of autosuggested results.
My hunch would be that the traffic is less for the autosuggested results
0
 
LVL 10

Accepted Solution

by:
NeoDiffusion earned 90 total points
ID: 22740539
Hello stevong,

If you're worried about over-loading your sql server by "useless" queries, I see 2 improvements axis:

  1. reduce Ajax calls; Usually, this is done by:
    • wait for a certain amount of characters before running ajax query (3 for instance)
    • do not run query until user stops typing for xx msec (500msec for instance)
  2. cache sql answer; efficency will vary depending on frequency of searchs. This can be done by managing an index DB or flat file(s) with query and answers.
Hope this helps,

Werner.


0
 
LVL 4

Assisted Solution

by:tvedtem
tvedtem earned 90 total points
ID: 22754007
NeoDiffs two ideas are both good ones.

If you are worried about the DB load though, best to arm yourself with some facts / timings from the DB.  That's pretty quick to do and will tell you how much you need to think about caching, delays etc.

I suspect you are worried that '%something%' doesn't lend itself to indexes - you'd be right to worry about that.  Options might include text-based indexing on your DB (depending what it is) - or if you can get away with 'something%' rather than '%something%' then so much the better.

Unless your tables or volumes are very large, though, you might find that it performs OK.
0
 

Author Comment

by:stevong
ID: 22812573
Problem is that it is really a huge database :(
0
 
LVL 10

Expert Comment

by:NeoDiffusion
ID: 22813067
Hello Stevong,


Having a huge database is another reason for applying the recommendations :-)

Note that "caching" does not mean "caching everything". In your context, caching will be there not to speed up delivery, but to reduce queries. As such, you don't need to have a huge cache. You can start by caching (storing) the answers from last 1 hour. If too few items cached, increase (for 2, 4, 8 or more hours), if too many, decrease (for 30min, 10 min, or less) cache validity duration.

Implementing such cache is pretty easy, even with "flat files".

Then, don't forget that you can also reduce request count by setting timeout before running an ajax query against your server.

Rgds,

Werner.

0
 
LVL 10

Expert Comment

by:NeoDiffusion
ID: 22813082
Note:
as tvedtem wrote, spending some time on analyzing (first: recording...) the usual requests  will help you uderstand where to put the emphazis: if all requests are different, caching is of no interest, for instance.
0
 
LVL 10

Expert Comment

by:NeoDiffusion
ID: 24126746
Hello angellll,

I believe that myself (neodiffusion) and tvedtem provided clear answers and follow'up.

I would rather suggest a point split.

Rgds,
Werner.
0
 
LVL 10

Expert Comment

by:NeoDiffusion
ID: 24174119
I personnally suggest Accept Expert posts as the answer, nameley id #22813067
0

Featured Post

Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.
Viewers will learn how to use the Hootsuite Dashboard.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question