We’re rewarding the question askers & reputations are being recalculated! Read more.
132

Premature optimization is "optimizing" something because of a vague, intuitive sense that, y'know, this will probably be slow, especially to the detriment of code readability and maintainability. It doesn't mean willfully not following well-established good practices regarding performance. Sometimes that's a difficult line to draw, but I'd definitely say ...


56

I think you misunderstood what indexing does for database performance. An index helps the database find rows. Indexes are specialized data-structures that, in exchange for extra disk-space and some performance when inserting and updating, help the database engine home in on matching rows. Because they take extra space and cost (a modicum of) performance to ...


51

BTree BTree (in fact B*Tree) is an efficient ordered key-value map. Meaning: given the key, a BTree index can quickly find a record, a BTree can be scanned in order. it's also easy to fetch all the keys (and records) within a range. e.g. "all events between 9am and 5pm", "last names starting with 'R'" RTree RTree is a spatial index which means that it ...


48

monitor for slow queries once we go live because nothing says quality like making your users suffer for a lack of design! You should know which queries need indexes when you design the tables, you know which columns are being queried on in where clauses and joins. These should be indexed already because what might not be apparent in a live environment may ...


35

Show the UI with index 1, use index 0 in code. That said, I worked with audio devices like this and used index 1 for the channels and designed the code never to use "Index" or indexers to help avoid frustration. Some programmers still complained, so we changed it. Then other programmers complained. Just pick one and stick with it. There are bigger problems ...


34

Yes. In fact, you very well may need to pay more attention to your indexes. Normalization is about optimal storage. That's often at odds with retrieval speed, as more complex queries with complicated joins are used. Sometimes people maintaining databases that require fast retrieval speeds will de-normalize, or arrange their data in slightly less ...


29

Basically, because an index is ordered, and when searching through an ordered data set, you don't have to search every item to find the element you're looking for; there are faster ways. Discussing database indexing in detail can become very arcane very quickly, but the simplest way to answer this is that you can use techniques such as a binary search to ...


26

"Premature optimization", in its derogatory sense, means costly optimization that might not be needed. It doesn't mean all optimization implemented before the latest possible point to prevent bankruptcy! In particular, it's legitimate to optimize based on performance tests before going live, to ensure you can meet some sensible (albeit approximate) ...


24

It feels like you're conflating the Identifier for the Channel with its position within a ChannelSet. The following is my visualisation of how your code/comments would look at the moment : public sealed class ChannelSet { private Channel[] channels; /// <summary>Retrieves the specified channel</summary> /// <param name="channelId"&...


21

One more index than you need is too many. One less is too little. I've tried searching for a case where having too many indexes was a problem and couldn't really find anything You KNOW you have too many if your inserts are too slow, and the index used for reading are not speeding things up enough to make up for it.


21

The technology you are looking for is full-text indexing. Most RDBMS have some sort of built-in capabilities which could work here, or you could use something like Lucene if you wanted to get fancier and/or just run it in memory.


21

Use both. Do not mix the UI with your core code. Internally (as a library) you should code "without knowing" how each element on the array will be called by the final user. Use the "natural" 0-indexed arrays and collections. The part of the program that joins the data with the UI, the view, should take care to correctly translate data between the User's ...


20

Instead of putting your data inside the DB, you can keep them as a set of documents (text files) separately and keep the link (path/url etc.) in the DB. This is essential because, SQL query by design will be very slow both in sub-string search as well as retrieval. Now, your problem is formulated as, having to search the text files which contains the set ...


20

I feel this is premature optimisation because our application is not even released yet. I suggested to monitor for slow queries once we go live and then add indices accordingly. You can't treat your end-users and production environment like quality-assurance. In more words, you're saying that you'll figure it out in production. I don't think that's the ...


17

Because your data is sorted, I would use a Binary Search algorithm on it. Since this is a text file (i.e., string lengths / record sizes are not all equal) you'll have a little adjusting to do. As an example, say your file is 1GB in size - (average string length including end of line is around 1,024 bytes. Open the file and start reading bytes at position ...


12

You can see the collection from two different angles. (1) It is, in the first place, a regular sequential collection, like an array or a list. Index from 0 is obviously the right solution then, following the convention. You allocate enough entries and map channel number to indices, which is trivial (just subtract 1). (2) You collection is essentially a ...


12

It's about offsets. You have an address, which points to the location in memory where the array begins. Then to access any element, you multiply the array index by the size of the element and add it to the starting address, to find the address for that element. The first element is at the starting point, so you multiply the size of the element by zero ...


11

It is not that the computer knows what the result is without reading the table. It actually does quite a lot of work to find the result, but it is very fast, so it appears instantaneous to you. But yes, certainly, it does not read the entire table. The way it works is implementation dependent, but a popular simple algorithm which serves for illustration ...


11

MongoDB is a database. Elasticsearch is a search engine. Since their aims are different, they have different priorities. MongoDB is focused on storing data consistently with good performance and to support different access patterns. Elasticsearch is focused on building low-latency indexes for search specifically text search. MongoDB does have full-text ...


9

Most, if not all, RDBMS automatically create an index on a FK because a FK means you will be doing joins using those columns. It's standard RDBMS behavior based on the fact that you will be using those columns in searches. A query with a join in it, even when absent a WHERE clause, is doing searches on the joined columns and comparing then with the PK of ...


8

Have you considered a trie? Basically you build a tree using common prefixes, so all words that start with the same letters are children of the same node. If you're going to support matching on any substring, then you'll have to generate some sort of permuted index and build your trie from that. That may wind up blowing your storage requirements way out, ...


8

The "index" of a book is not a great metaphor, IMO. A better metaphor is trying to look up a word in a dictionary. Imagine that you want to look up a word in the Oxford English Dictionary (OED), which is a massive dictionary that comprises multiple volumes. The words in the OED are sorted alphabetically, of course, to make it easy to look up a word. But ...


7

there is no 'magic number'. Every index after 0 slows down insert/updates a bit, but that shouldn't stop you from creating needed indexes. 37 does sound like a lot, make sure you're not using unnecessary indexes. For example, usually if you have an index on columns A, B you don't need a separate index on A. Or if you have an index on A, B, C, D you don't ...


7

At its core, a search engine index is simply an index that supports full text search. The most simple way to do that is a simple inverted index, i.e. for each word that occurs in any of the documents you have indexed, store a list of references to all the documents that contain this word. For a university project, that's probably enough, but of course ...


7

This is actually a very modest number of files for a doc management system. 5200 files x 52 weeks x 10 years is less than 3 million. Even at your own calculation, its only 1.5 TB of data over 10 years. That will easily fit on a hard drive. For this volume of files, I would recommend keeping the files in the file system, not the database. It will give you ...


7

Programming languages: Array[1] uses an implicit mapping between the index 1 a specific array element. This mapping is language specific. Many languages start at 0, some at 1. Some languages allow to start at an other offset. Some language implement sparse arrays. Some languages don't have general purpose arrays as a fundamental data structure and use ...


5

The purpose of database indexes is to increase the performance of searches in data tables. As you might already know, for unsorted data structures, the asymptotic notation O(f(n)) for searches is O(n). Meaning that, in the worst case, all rows of the table are going to be looked in order to rows matching the WHERE clause (and the same goes for JOIN columns)....


5

I would like add on top of Wyatt Barnett's answer that a RDBMS solution with full-text indexing on the appropriate column will work, but if you want to utilize a local cache of previously fetched records then you need to a plan to utilize these cached records to your advantage. One option is to collect the unique identifiers of these records that you ...


5

Go with your first approach, it is pretty much the standard way of modeling this type of relationship. (updated to record user who added the tag) Items ItemID (PK) ItemTags ItemID (FK) TagID (FK) Tags TagID (PK) UserTags ItemID (FK) UserID (FK) You could also do this by adding a UserID column to the ItemTags table and allowing duplicate rows for ...


5

If the Table of Contents in the front of the book is non-clustered, the page numbering itself (on the actual pages) is the clustered index. The "clustered" nature of an index indicates that the records are stored with the index nodes (or at least in the same order). So, the page-number analogy is actually very accurate in this respect, because like a real ...


Only top voted, non community-wiki answers of a minimum length are eligible