Skip to main content
Software Engineering

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

5
  • Hi Steve, thanks for your answer. MySQL already caches queries, correct? dev.mysql.com/doc/refman/5.7/en/query-cache.html I am using MySQL 5.7.19 so that's just before the query cache was deprecated. Commented Nov 11, 2017 at 18:03
  • Also, my table consists of an id column (primary key), name, and some other fields (i will only be searching by LIKE name in this scenario). This should suffice for the indexing, right? Commented Nov 11, 2017 at 18:05
  • Yes it caches query but that's still asking the database to do stuff which is heavy on servers/performance time compared to local cache. And no primary key indexing is not enough, your indexing strategy ultimately depends on the way searches will be done and what the data searched is. Commented Nov 12, 2017 at 12:39
  • 1
    Steve - I've seen your advise about server side pagination echoed many times and as a former RDBMS DBA, I cringe. Maybe I'm misuderstanding this, but if pagination is accomplished by breaking a single logical query into multiple queries by adding an additional WHERE clause or something that limits the range of results, this will slow down database access since it has to do many times the amount of work for a vastly greater number of queries. Commented Nov 19, 2017 at 7:56
  • 1
    Pagination isn't accomplished by breaking a single logical query into multiple queries. The point is to not query the whole 2000 results but instead query 10 or 20 results, since you don't want to display so many results at once. 10 or 2000 results should be retrieved with the same amount of queries. I 100% agree with you that too many queries is a really bad idea (which is one of the reason of sending queries after X characters were input :)). Commented Nov 20, 2017 at 9:01