Optimizing Database Structure value that occurs before the DST shift when clocks are changed, and one value that was occurs after the DST shift. In this case, the (converted) search value is matched only to index entries, and because the index entries for the distinct stored UTC values are also distinct, the search value can match only one of them. Due to different optimizer operation for nonindexed and indexed lookups, the query produces different results in each case. The result from the nonindexed lookup returns all values that match in the session time zone. The indexed lookup cannot do so: In the preceding discussion, the data set stored in  tstable  happens to consist of distinct UTC values. In such cases, all index-using queries of the form shown match at most one index entry. If the index is not  UNIQUE , it is possible for the table (and the index) to store multiple instances of a given UTC value. For example, the  ts  column might contain multiple instances of the UTC value  ’2018-10-28 00:30:00’ . In this case, the index-using query would return each of them (converted to the MET value ’2018-10-28 02:30:00’  in the result set). It remains true that index-using queries match the converted search value to a single value in the UTC index entries, rather than matching multiple UTC values that convert to the search value in the session time zone. If it is important to return all  ts  values that match in the session time zone, the workaround is to suppress use of the index with an  IGNORE INDEX  hint: The same lack of one-to-one mapping for time zone conversions in both directions occurs in other contexts as well, such as conversions performed with the  FROM_UNIXTIME()  and  UNIX_TIMESTAMP()  functions. See Section 12.7, “Date and Time Functions” . In your role as a database designer, look for the most efficient way to organize your schemas, tables, and columns. As when tuning application code, you minimize I/O, keep related items together, and plan ahead 1732 8.4 Optimizing Database Structure mysql>  SELECT ts FROM tstable IGNORE INDEX (ts) WHERE ts = ’2018-10-28 02:30:00’; +---------------------+ | ts | +---------------------+ | 2018-10-28 02:30:00 | | 2018-10-28 02:30:00 | +---------------------+ mysql>  ALTER TABLE tstable ADD INDEX (ts); mysql>  SELECT ts FROM tstable WHERE ts = ’2018-10-28 02:30:00’; +---------------------+ | ts | +---------------------+ | 2018-10-28 02:30:00 | +---------------------+ •If there is a usable index, comparisons occur in UTC. The optimizer performs an index scan, first converting the search value from the session time zone to UTC, then comparing the result to the UTC index entries: •It is performed within the storage engine, which knows only about UTC values. •For the two distinct session time zone values that map to the same UTC value, the indexed lookup matches only the corresponding UTC index entry and returns only a single row.