How do native queries effect evaluation during load and refresh?

[Anonymous]

Hello,

I am using the Odbc.Query to access a vendor's MySQL database. My query looks something like this:

SELECT x.a, x.b, z.c, z.d, w.e, w.f, z.g

FROM z

    JOIN y ON y.a = z.a

    JOIN x ON x.b = z.b

    JOIN w ON w.b = z.b

WHERE z.c = 1

    AND z.a IS NOT NULL

    AND w.e IS NOT NULL

    AND z.g = 2

    AND CHAR_LENGTH(w.f) = 3

The tables are pretty big, and I expect tens of thousands of rows as the result of this query.

The thing is, that if I run something like above, Power BI sits on 'Evaluating' forever. If I add a limit clause, like 'LIMIT 20000', it runs pretty quickly. Confusingly other queries I run return millions of rows and I don't have this issue with all of them

Can someone explain to me why this is? I'd like to optimize my queries for Power BI, but I don't understand what's happening during evaluation.

Thanks

[otravers]

I'd avoid running native queries if I can. If you want to leverage your database's capabilities, create views in MySQL that are optimized for BI purposes. Also, from the multiple joins in your query I wonder about what you're trying to load in Power BI. Are you trying to create on big table, or is this part of a star schema?