Solved

Create text file from mysql

Posted on 2013-06-14
6
358 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 109

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 109

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 55

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Backup Strategy 15 46
Help in good tutorials for PHP, HTML and CSS 6 43
php56-php-mcrypt for rhel7 php56 1 55
simple html dom php accessing table/cell  values 4 23
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
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…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

821 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