List:General Discussion« Previous MessageNext Message »
From:Jerome Abela Date:June 28 2000 1:48pm
Subject:Re: forcing index selection
View as plain text  
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.

Thread
forcing index selectionJerome Abela28 Jun
  • Re: forcing index selectionsinisa28 Jun
    • Re: forcing index selectionJerome Abela28 Jun
      • Re: forcing index selectionThimble Smith28 Jun
  • Re: forcing index selectionJan Dvorak28 Jun
    • Re: forcing index selectionJerome Abela29 Jun
RE: forcing index selectionQuentin Bennett28 Jun