When to add an index to joined tables

Question

I have a mysql table with 9 million records that do not have indexes set. I need to connect to another table based on a common ID. I am going to add an index to this identifier, but I also have other fields in the select and where clause .

Should I add an index to all fields in the where clause ?

What about the fields in the select clause ? Should I create one index for all fields or an index for each field?

Update - tables and query added

Here is the query: I need to get the number of sales, the item name and item ID for the item based on the store name and store ID (the name and store ID are not unique in themselves)

SELECT COUNT(*) as salescount, items.itemName, CONCAT(items.ID, items.productcode) as itemId 
FROM items JOIN sales ON items.itemId = sales.itemId WHERE items.StoreName = ? 
AND sales.storeID = ? GROUP BY items.ItemId ORDER BY salescount DESC LIMIT 10;

Here is the sales table:

+----------------+------------------------------+------+-----+---------+-------+
| Field          | Type                         | Null | Key | Default | Extra |
+----------------+------------------------------+------+-----+---------+-------+
| StoreId        | bigint(20) unsigned          | NO   |     | NULL    |       |
| ItemId         | bigint(20) unsigned          | NO   |     | NULL    |       |
+----------------+------------------------------+------+-----+---------+-------+

and table of elements:

+--------------------+------------------------------+------+-----+---------+-------+
| Field              | Type                         | Null | Key | Default | Extra |
+--------------------+------------------------------+------+-----+---------+-------+
| ItemId             | bigint(20) unsigned          | NO   | PRI | NULL    |       |
| ProductCode        | bigint(20) unsigned          | NO   |     | NULL    |       |
| ItemName           | varchar(100)                 | NO   |     | NULL    |       |
| StoreName          | varchar(100)                 | NO   | PRI | NULL    |       |
+--------------------+------------------------------+------+-----+---------+-------+

Answer 1

You must index all the fields that will be searched in the main table in the sentence WHEREand in the slave table in the WHEREand sentences JOIN.

Creating indexes to cover all the fields used in the query (including the SELECTand clauses ORDER BY) will also help, since table queries are not needed.

, , , , .

Update:

1 1 COUNT(*)

StoreID ( PRIMARY KEY) itemId StoreName ( PRIMARY KEY).

( 1 row), ( ).

, COUNT(*) 1.

, , .

, , .

2:

• sales (storeId, itemId)

• , PRIMARY KEY items (StoreName, ItemId) ( ).

  PK (ItemID, StoreName), items (StoreName, ItemID).

Answer 2

, , . , , .

9 milion , , , .

(storeid), (itemid, storename), ( , itemid), (itemid), (storeid), (itemid, storeid) ( storeid, itemid), , .

.

Answer 3

- . , .

, , , , .

, . .