Explore our sites

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I know this is a somewhat nebulous question but which algorithm would be more efficient and/or "faster"?

Searching through a preloaded JavaScript array of names or using the MySQL query "SELECT NAME WHERE NAME LIKE '%VARIABLE%'? The MySQL would be called using jquery AJAX to access a PHP file.

A little background on my website, it is a site primarily for mobile users and uses a mix of jquery and php to deliver content. In this case the user would be searching for a specific name within a list of ~22,000 unique names.

I have also thought about storing the table using LocalStorage as a cache, but then that requires an additional (though maybe less costly?) step of verifying/updating/loading the cache.

If more information is needed please let me know and thanks in advance.

EDIT:

A bit of extra information, the user can and probably will be searching for multiple non-unique values. For example: a search value of 'Jane' can and should return the results 'Jane Smith', 'Janet Smith', 'Jane Doe', 'Janess Whatver', 'jfhfuhd_JANE_dfifhf, Blah' (don't know who would ever have a name like that but if it was in the database and they searched Jane it should be return along with the others)

share|improve this question
 
how many times will the user use this functionality in an average session? how frequently? I'm voting for mysql. –  STT LCU Apr 15 '13 at 11:44
1  
MySQL with indexing on the names –  verbumSapienti Apr 15 '13 at 11:45
4  
Just a question: What's faster? Dumping 22k of data from the DB into an array AND have JS go over them? Or a trip to the DB looking for a single value? –  Joseph the Dreamer Apr 15 '13 at 11:45
1  
I would definitely use MySQL in this case - especially with mobile users, you can't always guarantee the speed of their device, whereas with MySQL you've got full visibility and can choose to optimize where necessary. –  max_w Apr 15 '13 at 11:46
1  
Another way to put forward this question, "Which one is faster and better on a mobile website, (1) executing an SQL query, yielding ~22,000 results, on each page visit and embed ~22,000 names in response every time a page is visited OR (2) make an ajax call to run an SQL query, yielding ~20 results, only when user wants to?" ? –  Ejay Apr 15 '13 at 11:49
show 3 more comments

closed as not constructive by Mr. Alien, Joseph the Dreamer, DaveRandom, Łukasz L., Lukas Knuth Apr 15 '13 at 12:41

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

up vote 3 down vote accepted

This is just speculation, but without testing, immediately I'd go for the MySQL query. MySQL has been optimised by its developers for the best search/sort algorithms - that's its job (one look at their benchmark manual will give you an idea as to just how much they care). The people who write the JavaScript interpreters won't have had as much time to devote to optimising their search/sort algorithms.

As an added plus, the Javascript option is dependent on the client's computer speed and browser - a slow device with a poorly implemented interpreted will take a much longer time. However, the MySQL option depends on your server, and therefore is completely under your control.

Some tests

JavaScript array with 10,000 values, comparing against a set value (jsfiddle.net/c6rpK/) - I get approximately 12ms

For the same test using MySQL I get 2.3ms

share|improve this answer
add comment
up vote 3 down vote

I'd go with MySQL. In fact, I wouldn't even think of trying to compare the two.

Databases are good at handling data: it's their job. Let them do it.

Storing the data locally has quite some disadvantages:

  • What if a piece of data changes? Will you re-post all data to all clients again?
  • You have to send all data to all clients to begin with.
  • Will the client have room (reserved) for that data?

The best of all of course is to index the names, either completely or partially.

share|improve this answer
 
I'm not super familiar with indexing, I'm planning on googling but could you give me a general idea of what are they/how they work? –  PseudoNym01 Apr 15 '13 at 11:54
1  
Indexing is the process, where a certain part of a record is placed in an index. Compare it to an index at the back of a book. Using that, you can get to a piece of information very fast, without having to browse through all the pages. Of course wikipedia has all the info. –  Bart Friederichs Apr 15 '13 at 11:56
 
From the MySQL page on indexes: The following SELECT statements do not use indexes: SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE other_col; In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant. My query string looks exactly the first query so an index would not be helpful it seems? –  PseudoNym01 Apr 15 '13 at 11:57
 
You could index in different ways. For example, you could create a table with words that are indexed and refer to the fullname table. –  Bart Friederichs Apr 15 '13 at 12:23
add comment

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