Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Create text file from mysql

Trying to come up with an easy way to select all records (150,000+) from a database and save these records in a json file where they have the same first 3 letters.
i.e. all entries starting with aaa... would be saved to a json file named aaa.json, all entries starting with aab... would be saved to aab.json and so on

This is to facilitate an autocomplete, which is just too slow at the moment due to the numbers and sending the data back and forth.
0
Gary
Asked:
Gary
  • 3
  • 2
2 Solutions
 
Ray PaseurCommented:
Usually in an auto-complete design the queries that run in the background script use LIMIT clauses, so you don't get something like 20,000 rows coming back when the client types the first letter.

You can probably create the JSON strings and store them separately, but you might consider using the LIMIT and adding an index on the column to see if that helps speed up the auto-complete.
0
 
GaryAuthor Commented:
I already have a LIMIT of 20 and the table is indexed, but still it's slow. This is my own code - no plugin.
I'm not talking majorly slow - have a look at www.book-your-trip.com and see if you think it is slow - maybe I'm being overly zealous in wanting it quicker.
0
 
Ray PaseurCommented:
It didn't look slow to me as I typed "Dublin" but it came back with some off-the-mark results.  For example, as I got to "Dubl" it suggested, inter alia, Dubrovnik, Dubai, Audubon, Pardubice, Hronska Dubrava...  It looks like it was searching for "dub" anywhere in the name.  I would have expected it to be searching for "dubl" but that did not appear to be the case.

I think I might winnow the list down some, generally offering a preference for matched characters at the beginning of the string.  That would assume that the client knew what they were looking for.

Nice looking site, BTW!
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Julian HansenCommented:
Initially it looked slow but then I realised it was because it was Friday and that means my brain is only half started.

I was typing 3 characters and waiting but it appears to load after 4.

Average turn around was about 670ms - given I am a remote user with average ping times of 250ms - I don't think that the response time is slow.

By comparison - Google autocomplete takes between 350 and 1.5 seconds on a similar ping time.
0
 
GaryAuthor Commented:
Ok I'll leave it as it is then.  
There seems to be some glitch in the ajax and it's sending old data hmmm

p.s. Thanks

Edit.
Seems keypress was triggering incorrectly. So Dubl works fine now and it is firing after 3 characters.
0
 
GaryAuthor Commented:
If you guys are interested
http://www.experts-exchange.com/PR_301.html
First time using this!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now