CODEWITHSUNDEEP
phpmysqlselect
aslamdoctor
aslamdoctor

Reputation: 3935

How to select records faster from a big database?

I have a mysql table of postcodes with 200000 Records in it. I want to select just one field post_code from it and fetch the postcodes for auto suggestion in a textbox. I will do this using jquery but I want to know which method I should use to create code in php and select records faster?

Upvotes: 2

Views: 2030

Answers (4)

Baz1nga
Baz1nga

Reputation: 15579

you probably want to filter the records that matches the current input of the user and your query should be as follows:

select postal_code from table_name where postal_code like '<userinput>%' LIMIT 50 eg: '60%'

and send back this result set to your jquery callback function. remember to set a limit on the postal codes retrieved as you dont want to send back too much data which not only hampers the performance but doesnt help the user in a direct way.

Upvotes: 0

GordonM
GordonM

Reputation: 31730

Without specifics it's hard to give an answer more specific than "Write a faster query".

I'm going to make a huge assumption here and assume you're working on a web application that's populating an autocomplete form.

You don't need all the post-codes! Not even close.

Defer populating the autocomplete until the user starts typing into the postcode field. When that happens, do an AJAX load to get the postcodes from the database.

Why is doing it this way better than just fetching all the post codes?

Because you now know what letter the user's post code starts with.

Knowing the first letter of the post code means you can eliminate any post code in your dataset that doesn't start with the same letter. You'll go from having to fetch 20000 postcodes to having to fetch less than 2000, improving performance by over an order of magnitude. If you wait until the user has entered two letters, you can get the amount of data to be fetched down even further.

Also, I'm assuming you're using an index on your postcode column, right?

Upvotes: 2

Wasim Karani
Wasim Karani

Reputation: 8886

You can use Sphinx-Php Api to deal with big database
But you will need to indexer file for your search for the first time to use this way.
Or can go for Zend Lucene Search

Upvotes: 0

paulsm4
paulsm4

Reputation: 121649

I would think you simply want to create a MySql index on "post_code"?

Q: How many postal codes are you planning on fetching at a time (and passing back to the browser)? One? Five? 200,000?

Upvotes: 0

Related Questions