Solved

Create text file from mysql

Posted on 2013-06-14
6
355 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 52

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to count occurrences of each item in an array.
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…

895 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

14 Experts available now in Live!

Get 1:1 Help Now