Db2 – db INDEX on ORDER BY FIELD in very simple query does not help – still slow response

You may want the following indexes

ALTER TABLE tran.member ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.member ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);

Then, you could queries like these

Each Member and All Cards for That Member

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.member GROUP BY mbr_nbr;

Each Card and All Members Associated with that Card

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.member GROUP BY cardnbr;

So, that the size of tran.member does not slow things down, try creating a temp table for the purpose of gathering these two columns

DROP TABLE IF EXISTS tran.adhoc_members_cards;
CREATE TABLE tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENGINE=MyISAM;
ALTER TABLE tran.adhoc_members_cards ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.adhoc_members_cards ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);
ALTER TABLE tran.adhoc_members_cards DISABLE KEYS;
INSERT INTO tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.memberl
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENABLE KEYS;

Now, you can run the two queries I mentioned like this:

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.adhoc_members_cards GROUP BY mbr_nbr;
SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards GROUP BY cardnbr;

Search for the cardmember you want as well:

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards WHERE cardnbr = 000099999930;

Your query actually is using the idx_ksours index.

It seems like a pretty sensible assumption that Using index in the Extra field means the query is using the index, and then when that isn't shown in Extra then the index isn't being used... but that's actually not what that means.

Indexes, as you likely know, contain copies of the indexed column(s) data, in sorted order, along with a copy of the primary key of the row so the row referenced by the index can be retrieved.

When all of the data the query needs to examine in a table is available inside one index, the optimizer realizes that reading the actual row data is unnecessary -- everything needed is actually contained in the index, and will be read from the index -- eliminating the extra lookup to retrieve the associated row from the full table. This is what Using index means.

On the other hand, if the optimizer will using an index to optimize the query, the name of that index is shown in key column of EXPLAIN SELECT.

+------------+
| key        |
+------------+
| idx_ksours |
| postid     |
+------------+

So, you're actually using idx_ksours.

That's the good news. The bad news, of course, is that this index doesn't offer enough of an optimization for your entire query.

Note that the second column in idx_ksours is visible, which is not evaluated by anything in the join condition or where clause, which means none of the other columns in that index are not used for this query -- it's only helping you find rows WHERE post.threadid = 562942.

The comment from @dezso addresses this -- if you had an index with all-relevant columns, in a helpful order, that index would provide more benefit for the query.