Solved

Create text file from mysql

Posted on 2013-06-14
6
363 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
[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
  • 3
  • 2
6 Comments
 
LVL 110

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 110

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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 57

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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 article discusses how to implement server side field validation and display customized error messages to the client.
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 a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

726 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