I have a query where I'm selecting a large dataset and it's taking an extremely large amount of time because of the one-to-many relationships of the joined tables. I have three tables--simplified they are as follows:

customers
---------

```
| customers | CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(128) NOT NULL DEFAULT '',
  `username` varchar(128) NOT NULL DEFAULT '',
  `usertype` char(1) NOT NULL DEFAULT '',
  `password` varchar(255) NOT NULL DEFAULT '',
  `membershipid` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `membershipid` (`membershipid`)
) ENGINE=MyISAM AUTO_INCREMENT=980 DEFAULT CHARSET=latin1 |

```

memberships
-----------

```
| memberships | CREATE TABLE `memberships` (
  `membershipid` int(11) NOT NULL AUTO_INCREMENT,
  `membership` varchar(255) NOT NULL DEFAULT '',
  `flag` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`membershipid`),
) ENGINE=MyISAM AUTO_INCREMENT=316 DEFAULT CHARSET=latin1 |

```

membership\_contracts
---------------------

```
| membership_contracts | CREATE TABLE `membership_contracts` (
   `bond_id` int(11) NOT NULL AUTO_INCREMENT,
   `membership_id` int(11) DEFAULT NULL,
   `contract_id` int(11) NOT NULL,
  PRIMARY KEY (`bond_id`),
  UNIQUE KEY `membership_id` (`membership_id`,`contract_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1623 DEFAULT CHARSET=utf8 |

```

contract\_items
---------------

```
| contract_items | CREATE TABLE `contract_items` (
  `contract_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `price` decimal(12,2) DEFAULT NULL,
  `start` int(11) DEFAULT NULL,
  `end` int(11) DEFAULT NULL,
  `contract_item_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`contract_item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=27398 DEFAULT CHARSET=utf8 |

```

There is a one to many relationship between the membership and contracts, and the contracts and contract items.

my current query is:

```
SELECT customers.username, contract_items.contract_id, so_on_and_so_forth
FROM   customers, memberships, membership_contracts, contract_items
WHERE  customers.membershipid=memberships.membershipid
AND    memberships.`membershipid=membership_contracts.membership_id
AND    membership_contracts.contract_id=contract_items.contract_id
AND    contract_items.item_id=user.item_id

```

so I have a small dataset (users and items) that's increased 600,000 lines (each membership has around 25 contracts and each contract can have over 1000 items) to only select items that are on a contract that the user has on their membership.

Is there a faster way to do this? Am I using a join that I shouldn't be? I have a unique index on membership\_contracts for the ids, so at least that's minimal.
 |

```
| id | select_type | table                | type   | possible_keys            | key           | key_len | ref                                                                                             | rows  | Extra                           |
+----+-------------+----------------------+--------+--------------------------+---------------+---------+-------------------------------------------------------------------------------------------------+-------+---------------------------------+
 |  1 | SIMPLE      | contract_items       | ALL    | NULL                     | NULL          | NULL    | NULL                                                                                            | 24691 | Using temporary; Using filesort |
 |  1 | SIMPLE      | order_details        | ref    | orderid,productid        | productid     | 4       | database123456789.contract_items.product_id                                                   |     2 |                                 |
 |  1 | SIMPLE      | orders               | eq_ref | PRIMARY,odsp,ospd,userid | PRIMARY       | 4       | database123456789.order_details.orderid                                                       |     1 |                                 |
 |  1 | SIMPLE      | customers            | eq_ref | PRIMARY,membershipid     | PRIMARY       | 4       | database123456789.orders.userid                                                               |     1 |                                 |
 |  1 | SIMPLE      | membership_contracts | ref    | membership_id            | membership_id | 9       | database123456789.customers.membershipid,database123456789.contract_items.contract_id       |    12 | Using where; Using index        |
 |  1 | SIMPLE      | memberships          | eq_ref | PRIMARY                  | PRIMARY       | 4       | database123456789.membership_contracts.membership_id                                          |     1 | Using where                     |

```