On 1 Aug, 15:07, Jean-Baptiste ANNE <jea...@gmail.com> wrote:
>
> What stats do you need ?
Well, it is good to know how many rows from each table may match some
of the tests, so as to ensure that the choices are done in the most
efficient way.
How many rows in img have deleted = 0?
How many rows in there in total?
does deleted hold only 1 or 0?

> But, you could try the folowing:
>
> > On your img table, build a unique composite index of deleted,id. Since
> > this is the "prime" table in the query, this will stop it having to
> > read all 35k of the records to find which ones are not deleted.
>
> I created this idx and I have to examine 26k rows on 'img'. (the same
> before).
The explain you posted showed 21k rows. Where are you seeing the 26k?

> The other thing that could speed it up might be to remove the
> > SQL_CALC_FOUND_ROWS if that is acceptable. then it would just stop
> > when the limit had beed reached.
>
> SQL_CALC_FOUND_ROWS seems to take about 10ms (by benchmarking with a
> quick query)
I don't understand what you mean by "benchmarking with a quick query".
SQL_CALC_FOUND_ROWS must count all the possible results that would be
returned by the query if the LIMIT clause were not there.


which is acceptable and I prefer add this than querying
> MySQL again with "SELECT COUNT(*) ....".
>
> Thanks a lot,