Solved

Create text file from mysql

Posted on 2013-06-14
6
354 Views
Last Modified: 2013-06-14
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
Comment
Question by:Gary
  • 3
  • 2
6 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39247688
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
 
LVL 58

Author Comment

by:Gary
ID: 39247719
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
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 39247820
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
ID: 39247881
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
 
LVL 58

Author Comment

by:Gary
ID: 39247898
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
 
LVL 58

Author Comment

by:Gary
ID: 39249374
If you guys are interested
http://www.experts-exchange.com/PR_301.html
First time using this!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now