2

Im new to databases, so please just dont blast me.

I studied that MySql creates automatically an index for the primary key. But, if the index is used to speed up searchs and joins and the primary key is always used in this 2 operations, why should we also create index for other columns ?

Tantaros
  • 113
  • 1
  • 7
  • 1
    Create indices when they are required to speed up searches - by being usable to quickly reduce the problem/table/data space - if/when the PK is not sufficient to enable the desired queries to be *SARGable* (search for it) .. and way too broad. – user2864740 Jan 12 '18 at 08:07
  • If you want to search `SELECT * WHERE secondColumn = specificValue` -> if you don't have some index including the secondColumn, then index on primary key / other coclumns are useless to search quicker. Full table scan will be used. – Pac0 Jan 12 '18 at 08:46

2 Answers2

0

For instance, you might want to search (order results or filter results in where clause) by another column, say People.Name or People.LastName. In order to do that much quicker, you can create an index with this column. It might not even include the primary key if you don't need it.

The thing is that data in the index is stored in a different way than in the table, specifically in order to speed up searching by the index.

Sergey Benzenko
  • 280
  • 2
  • 14
0

Joins are not only PK-to-PK. Any 2 tables can be joined on any condition involving any of their columns. PKs (primary keys) & other constraints and indexes are not needed for joining. They are for integrity & optimization.

Tables represent application/business relation(ship)s/associations. A query returns a table that is a combination of base tables. That table represents a relation(ship)/association that is a corresponding combination of those base tables' relation(ship)s/associations.

What Kind of Relationship is Between These Tables?
Required to join 2 tables with their FKs in a 3rd table

philipxy
  • 14,867
  • 6
  • 39
  • 83