sql - Index for multiple columns in ActiveRecord -

-

There are two ways to declare an index for multiple columns in the Active record: 

 add_index: classification, [: Species: family: trivial_names] 
 add_index: taxonomy, species add_index: taxonomy, family add_index: taxonomy,: trivial_names

what is the first approach and second? If so, when should I first use and second when?

You are comparing a composite index to a set of independent index they are just different.

Think about it: A complex index lets you quickly see the first field in the nested set of the area, after which the second field looks fast the first field already by Within the selected records only, after the fast field view of the third field - then, only within the records selected by the previous two indexes

We take an example. If your database engine does not take more than 20 steps to detect a unique value within 1,000,000 records (if memory works), then if is using any index, it is true that Whether or not you are using a composite or independent index - but only the first area (in your example "species" though I thought that you want you family, species, and then the common name Want).

Now, suppose that there are 100,000 Milan records for this first field value. If you have only one index, any search within these records will take 100,000 steps: The reason for each record retrieved by the first index is that the second index will not be used (mostly in the database - this is a bit of simplification. ) And a brutal force match should be used.

If you have the overall index then your search is very fast because the first area of ​​the value in your second field search will be set before in this case You will not need more than 17 steps to get your first match value on field 2 in Field 100 of 100,000 on field 1 (100,000 log base 2). Therefore: Stages needed to find 3 unique records on a nested field, using a unique index of 1,000,000 records, a unique record of the first 100,000, and the second 10,000 = 20 + 17 + 14 = Receives 51 steps.

Required steps under these conditions only with independent index = 20 + 100,000 + 10,000 = 110,020 steps.

The big difference, eh?

Now, the not hybrid index is everywhere first, they are expensive on inserts and updates. Second, they are only brought to bear if you actually search in nested data (For another example, I use the time when dragging data to log in for a date for a customer). Also, if you are working with relatively small data sets, then they are not worth it.

Finally, check your database documentation. These days the databases have become very sophisticated in the ability to adjust the indices and the Database 101 scenario I mentioned above may not be for some (although I always develop it like this is just because what I know I am).


Comments

Post a Comment

Popular posts from this blog

c++ - Linux and clipboard -

-
After selecting the copies of the buffer in Linux, after the text, we can click and paste the button between the mouse. I think there is a special buffer for this, I want to use it. Programming Language: C ++ Your Library: Qt Thanks. There is just one more correct answer than Paul Dixon, which answers your needs: / P > QClipboard * clipboard = QApplication :: clipboard (); CastString Selected Text = Clipboard-> Text (QClipboard :: Selection);
Read more

What is expire header and how to achive them in ASP.NET and PHP? -

-
I have examined the performance statistics of my website today. I have received a warning (or error may occur) that is given below Add Terminal Headers Here are 15 static components without a 15-futures end date * (no end) Http://www.example.com/video/css/global.css * (no time limit) http://www.example.com/video/js/global.js * (no expiration ) Http: //www.example.com/video/images/main-bg.png what it means and how to achieve it both in PHP and ASP.Net. I am on a shared hosting server, so please tell me some ways to use the code, because I can not make any modifications at the end of the server. If I expire the header, then there is no chance that if I make changes to the CSS, then the user will not be able to remove them immediately because CSS and other files have a fixed time limit (1 month, week ) Are cached for. Is Is there any harm to using PHP? $ time = time () + 3 * 24 * 60 * 60; // 3 day headings ('End:'. GMDAT ('D, D MYH: i: s \ g \ m \ t',
Read more

sql server - How can I determine which of my SQL 2005 statistics are unused? -

-
After a few years, one of my biggest databases has deposited 73 figures out of its largest tables. With Indexes, I can run many types of reports and queries, how often / heavily specific indexes are used. What is an equivalent for data? We are running SQL 2005. To make statistics automatically managed and honest, I would like to "update statistics", "create statistics" and There is no information about any management except "drop statistics" orders. In theory, UPDATE should handle the addition and removal of statistical data. In addition, I've never heard about the amount of storage or storage taking in large amounts, so I'm not sure whether there is any reason for the alarm or not. If you are seeing a potential problem, then I would suggest running an update before, if it does not clean it, it is leaving and then it appears to be safe operation (because it is the only query optimizer Is for). I will not leave "left" in the
Read more