CODEWITHSUNDEEP
phpjavascriptmysqldatabase
Tzvi
Tzvi

Reputation: 343

How to optimise storing and fetching from a huge list in php?

Thanks in advance for helping,

I have a list of one million names and nicknames, each name has only one nickname. each name has it's corresponding nickname (even if it's the same)

I normally use PHP and MySQL to store data in rows and then use a select statement to fetch. I consult you because of the volume this method might not be optimised.

A script will be created that needs to get each time the corresponding nicknames of about 600 names out of the million, this will be executed with each user pageload.

I have experience in working with PHP and MySQL

I can construct the data in whatever way is best, plain text, mysql or something else.

Whats the best way to store and fetch this data set ?

Upvotes: 0

Views: 143

Answers (2)

Joseph
Joseph

Reputation: 119867

  1. Pagination / Lazy-loading

    Never load all items at once. Have them load incrementally. A real life example would be Facebook and Twitter. It only loads a set amount of items, then when you reach the bottom or click on "load more", it loads the next N items or Google, which only shows N items per page out of a billion possible results.

  2. Use JSON

    These days, I still see people return formatted HTML in AJAX requests - don't do that. Have your server load the initial page (which has initial HTML), and the rest in JSON via AJAX. Have some client-side templating script create the HTML for you when the JSON data arrives. This saves you bandwidth and download time.

  3. Use Compression

    It's pretty obvious why you should use compression.

  4. Load only the required fields in SQL

    Often times, you load all columns because "they might be useful someday" - no. If you want to load a set of nicknames, have SQL load only the nicknames. Of course, the primary key field is always required for pairing, so in this case, 2 columns.

And seeing that it's only a 1-to-1 relationship between the nickname and the person, store it in the same table. There is little need for it to be in another table. This saves you from a JOIN operation.

Upvotes: 2

tonymarschall
tonymarschall

Reputation: 3882

As eggyql mentioned add in index to your name column. Also set a limit to your sql select SELECT name, nickname FROM your_table LIMIT 0, 10

Upvotes: 0

Related Questions