Re: Performance problem with outer join

[RT Lee]

Rick - thank you so much. Problem solved thanks to you!!

alter table datausage CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

followed by some optimize table statements.

Query now runs in 0.15 seconds instead of 2 seconds. There are also some counts we had to do which essentially ran a similar query, so it was taking like 6-8 seconds and now its well under 0.5 seconds total for all queries related to this report.

Thanks again!


Rick James Wrote:
-------------------------------------------------------
> > | 1 | SIMPLE | datausage | ALL | NULL
>
> | NULL | NULL | NULL | 96805
> | Using where; Using join buffer (Block Nested
> Loop) |
>
> Note "ALL" and "NULL". That is saying it is not
> even using the index to do the JOIN. Here's why:
>
> `accountID` varchar(25) COLLATE utf8_unicode_ci
> NOT NULL ... DEFAULT CHARSET=utf8
> `AccountID` varchar(25) ... DEFAULT CHARSET=latin1
> ;
>
> Inconsistent CHARSETs make it 'impossible' to use
> the index. Hence it has to scan Datausage again
> and again.
>
> Make the schema consistent.