Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am running what I think is a very simple query:

SELECT SQL_NO_CACHE m.mbr_nbr
FROM tran.member m
WHERE m.cardnbr = 000099999930

The table member is a poorly written one, with 133 columns and there are about 2 million rows. The query is being run in a program to gather the mbr_nbr for 20,000 card numbers. So, the query is being run 20,000 times. Is there a better way to handle this or should I try to optimize the query? Maybe by creating an index on the two fields I need?

Rebuilding a better database isn't an option.

Neither field is the primary key.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.