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)

Solution 1:

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.

Solution 2:

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