On Wed, Jun 28, 2000 at 03:07:25PM +0300, sinisa@stripped wrote:
> > > mysql> explain SELECT count(*) FROM data d, subnames s WHERE
> s.subname='alvar' AND d.name=s.name AND d.place='paris';
> > > | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> > > | s | ref | indexsub | indexsub | 10 | alvar |
> 3 | |
> > > | d | range | nameindex,placeindex | placeindex | NULL | NULL |
> 26481 | |
> > > 2 rows in set (0.00 sec)
> >
> > Ok, so it seems that 101097 (which come from nowhere ?) was compared to
> > 26481, and that nameindex key (the correct one, with only 5 real matches)
> > was eliminated in favor of placeindex, which really matches for 26481
> > rows.
> >
> > Does anyone have any idea on what I can do to improve this SELECT ?
> > I would love to be able to say: "for this request, please use nameindex",
> > or "for this request, please don't use placeindex". I'm always able to
> > either:
> > 1. know in advance which one is faster, or
> > 2. previously SELECT the size of both request to do the job of the
> > optimizer, and return to 1.
>
> What is your MySQL version ??
> Can you try above with 3.23.19 ??
It was 3.22.32. I upgraded to 3.23.19, but there is no difference: the
request is very badly optimized. I don't care about mysql not
optimizing correctly the search, but when I know how the search
should be done, I would like to share those hints with mysql.
Jerome.
