1

I have a table with the following structure below:

Id (Int), Telephone_Number (Int), Name (VARCHAR), Address (VARCHAR)

I wish to create a covering index for this SELECT query:

SELECT Id FROM mytable WHERE Telephone_Number = '55442233';

I understand covering indexes remove the amount of Disk I/O as the indexes are stored in the memory cache, therefore increasing performance.

For my SELECT query, am I right in saying making Id an index will not remove the need for Disk I/O due to the WHERE clause needing access to the Telephone_Number value?

If so, would a composite index of (Id, Telephone_Number) work as a covering index? Or do covering index have to be single column's?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Babra Cunningham
  • 2,949
  • 1
  • 23
  • 50
  • I think you should also check what is an `Index` because they increase performance because they created a b-tree making search faster. Doesnt have anything to do with memory cache. – Juan Carlos Oropeza Nov 08 '16 at 19:07
  • The columns in your index need to be reversed. Apperently, you are lacking quite some basics about indexing. You might want to read my (free online) indexing book: http://use-the-index-luke.com/sql/table-of-contents — Covering indexes are explained in chapter 5 in the section "index only scan". – Markus Winand Nov 08 '16 at 20:29
  • You might like my presentation [How to Design Indexes, Really](http://www.slideshare.net/billkarwin/how-to-design-indexes-really), and the video here: https://www.youtube.com/watch?v=ELR7-RdU9XU – Bill Karwin Nov 08 '16 at 22:06
  • 1
    Index blocks are _cached_ in RAM, as are data blocks. They are not _stored_ in RAM. The covering index you need is in the opposite order: `INDEX(telephone_number, id)`. [_Cookbook_](https://mariadb.com/kb/en/mariadb/building-the-best-index-for-a-given-select/) – Rick James Nov 13 '16 at 19:44

0 Answers0