How is it right to select the indices for the tabulation of the database on the lead example?



  • There is a table in it Id with an ink period, several different text fields, and the main is is 2 int fields with a certain number of ranges that are not intersected in other records of this table (this is not required to control, the data is so downloaded that the ranges do not intersect). So the first field keeps the range start number and the second end. Let's give the parole field. idminmax

    I'm interested in SELECT. id FROM table WHEREmin PER N) AND (max var N); where N is a certain submerged number.

    The question is not only how to choose an index. min and max Pola, why? Based on what I read in the books, it's probably better to make one index for both fields. min and maxI didn't understand why. In my view, if you make separate fields, it's kind of clear that it's supposed to work normally. I just imagine that I'm looking for one index on the other, and then I'm taking them together? And how does a sample work in cases with a composite index, how can you describe theory itself or even the physical meaning of how the combined index works? If you can in any way dispense with the example of any 10 entries, that is, how they will look like an index and make it clear that it is actually faster than two indices in two fields.



  • I'm interested in asking for a species. SELECT id FROM table WHERE (`min` < N) AND (`max` > N); where N is a certain amount.

    The question is not only how to choose the index for min and max field, but why?

    Right in terms of productivity, change storage. Use GEOMETRY and Spatial Index accordingly.

    If this is not possible, the cover index should be used for (`min`, `max`, id) or (`max`, `min`, id)

    Based on what I read in the books, it's probably better to make one index for both fields min and max.

    Bad reading. You have inequality - the index will therefore be used only for one condition for which the field in the first index.

    In my view, if you make separate fields, it's kind of clear, too, what should work normally.

    Wrong. One copy of the table at the source of the request can only use one index.


    In terms of maximum efficiency, with the guarantee that the ranges do not intersect (i.e., the request returns either one recording or an empty data set) and, if not to use spatial datatypes, the request should be rewritten as follows:

    SELECT id
    FROM ( SELECT id 
           FROM table
           WHERE `min` < N
           ORDER BY `min` LIMIT 1 ) t1
    JOIN ( SELECT id
           FROM table t2
           WHERE `max` > N 
           ORDER BY `max` DESC LIMIT 1 ) t2 USING (id)
    

    and create two indices - (`min`, id) and (`max`, id)♪ But this exercise only makes sense if the table is really, really, really big (billion-billion-billion-dollar hygigabytes).



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2