I have some JSON data in this format (5,000 instances for now):

{"id":"123456","icon":"icon.png","caseName":"name of case"}

I am allowing the user to search for the name of the case and then return the id and icon to them, and in some situations, use the case id to run other functions, based on the search.

How should I read this data? I'm currently leaning towards a switch because I want the switch to allow me to define multiple cases giving me the same result:

name of case and name-of-case should return me the id 123456

I can easily use a switch based on the string, listing out different cases, to do this. Probably going to be extremely tedious, but it's an option.

So, my options I can think of are as follows:

  1. Run a massive switch based on the query string (probably in a separate PHP file)
  2. Store this result in a MySQL database, then based on the query string, retrieve the data, and I probably need to do indexing (which I understand, but have no idea how to implement).

Also, I would like to prepare for the scenario that this 5,000 instances increases to 10,000 instances at some point in time.

What's the best way of going about this?


  • 1
    I don't understand “the name of case and name-of-case should return me the id 123456”. Does it mean that caseNames uniquely identify the entities, but ids are not? If so, what's the point of using the ids? Feb 12, 2014 at 7:40
  • @MainMa - No, IDs uniquely identify the cases, what I meant is to allow alternative ways of passing the query. If I search for "name of case" or "name-of-case" I receive the ID 123456. Doing this with a switch statement is easy by just stacking the different case matches above one another. Feb 12, 2014 at 7:42
  • 2
    Am I understanding you correctly that you want to dynamically build a php script with a very big switch-tree? Aren't you then basically making your own (very imperfect) database engine? I'd say use a database, your problems have been solved by others, no need to re-solve them.
    – Pieter B
    Feb 12, 2014 at 8:12
  • @PieterB - Now that you put it like that, yeah, it makes sense to use a normal database. Thanks for the enlightenment. Feb 12, 2014 at 8:15
  • 3
    You could always use SQLite if you wanted to avoid having a separate DB server. Feb 12, 2014 at 11:48

3 Answers 3


Always use SQL. The amount effort to read the data from SQL is in your case of complexity similar with a query. So why bother to add extra code?

  • 3
    It'd be a lot more flexible too, making more complex searches easier to do. Feb 12, 2014 at 8:05
  • 2
    It'll be very fast. Honestly, even unindexed it'll likely be faster than manually doing the search in your code. Feb 12, 2014 at 8:20
  • 4
    @user3201185: In the grand scheme of things, 10,000 is not very many items at all. Databases are built to handle much much higher loads while maintaining high performance.
    – Phoshi
    Feb 12, 2014 at 9:11
  • 2
    @user3201185 it'll probably be measured in milliseconds, and most of that will be network time for such a simple search. Feb 12, 2014 at 16:05
  • 1
    While flexibility in the future may very well be a bonus, if you don't need it and your sizes truly are limited (say up to 20K), please consider using an in-memory map as was suggested below! Depending on how you're slurping in the data, it will be extremely fast to build in memory and lookups should be say <1ms on any kind of decent machine! To put this in context, I just wrote a .Net app using Dictionary that did 10K string-based lookups in 1ms. Your speed with PHP may vary, but your biggest adversary will be change in requirements.
    – J Trana
    Feb 13, 2014 at 2:53

If you take pride in your work, then:

  • the overhead of doing an database query every single time should disgust you
  • the idea of maintaining a massive hard-coded switch should horrify you

Most languages have support for collections and include things like hash maps, and this is likely to be very well optimised (and for languages that don't it's relatively trivial to implement anyway). This is what you should be using. For a collection of 10000 items it's extremely likely that you can find a value from your key faster than you can send a "do nothing" request to a database and receive a "nothing done" reply back.

The next question is, when you're creating the collection where does the data come from? This depends on the nature of your software - does it start once and handle many requests (like virtually everything that is sane), or is your software started from scratch every time (e.g. like the old CGI interface web servers used to use back when "internet connection" meant a dial-up modem; where every single "page fetch" would cause a massive amount of bloat starting a script interpreter, finding the script, letting the script initialise itself, etc).

For the first case you can do whatever is easiest for you to maintain. This could be loading the data for your collection from a "plain text" file or from the database. Performance doesn't really matter here because it only happens when your software is first started (which might be once every 200 days or something).

For the second case; you should ignore everything I've said above and do a database lookup every single time. Even though this should disgust you, anything you could do in your own software (starting/loading/initialising everything from scratch every single time) should disgust you even more.


Listing 10000 cases manually is a titanic work. You should use a database.

If more than one spelling is possible, you can use an Alternatives table, to fetch the canonical version of a case name.

  • Thanks, now I know where to begin, how does it work though? I mean if I have this other table of data, that means that I have 2 instead of just 1 storage of all this data, and if I have more than 2 ways of spelling it, more tables? Oh and what do you mean by "fetch the canonical version of a case name"? Feb 12, 2014 at 8:27
  • 1
    I am sorry I do not understand your question.. what "other table of data" ? you can combine queries with multiple tables, make joins and what not... and even with 1 milion records will still be fast. Feb 12, 2014 at 9:16
  • @AdrianPirvulescu - Alright, noted, thanks. I'll have to read up more about it. Feb 12, 2014 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.