Speed Up MySQL Query Using Where Clause

This is going to sound obvious but to me it wasn't at first. The situation was, I wanted to see the top 1000 rows of a join on three tables, all of which had many thousands of records in them. The query did not finish in reasonable time until I added a where clause that didn't limit the result set in any way, but certainly sped up the response.

Here's an example to demonstrate the details.

My tables looked similar to this:

CREATE TABLE `Customer` (
`cId` int(11) NOT NULL auto_increment,
`cName` varchar(255) default NULL,
PRIMARY KEY (`cId`)
)

CREATE TABLE `PaymentBatch` (
`pbId` int(11) NOT NULL auto_increment,
`pbStatus` varchar(255) default NULL,
`pbCreatedDate` datetime default NULL,
`pbCompletedDate` datetime default NULL,
PRIMARY KEY (`pbId`)
)

CREATE TABLE `PaymentBatchDetail` (
`pbdId` int(11) NOT NULL auto_increment,
`pbdPaymentBatchId` int(11) NOT NULL,
`pbdCustomerId` int(11) NOT NULL,
`pbdReceivedDate` datetime default NULL,
`pbdAmount` decimal(12,3) default NULL,
PRIMARY KEY (`pbdId`)
)


And this was my first try at the query; I just wanted to see the most recent 1000 rows from the join of these tables:

select *
from Customer left join
(PaymentBatch left join PaymentBatchDetail on pbId = pbdPaymentBatchId)
on cId = pbdCustomerId
order by pbdReceivedDate desc
limit 1000;


Well, this took so long I killed it before it completed.

But adding a simple "where pbId > 0", which doesn't eliminate any rows from the query, made it run in under two seconds. Go figure.

Faster query:

select *
from Customer left join
(PaymentBatch left join PaymentBatchDetail on pbId = pbdPaymentBatchId)
on cId = pbdCustomerId
where pbId > 0
order by pbdReceivedDate desc
limit 1000;