You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@doris.apache.org by "cunenor@qq.com.INVALID" <cu...@qq.com.INVALID> on 2023/07/18 08:59:04 UTC

encountered some problem when using mysql catalog

Hi Team:
recently, when using mysql catalog,  I encountered some problem. is the way that I apply catalog wrong or something else? 

does anyone can take a look at this?

issue link : [Bug] when using mysql jdbc catalog, I find the unconcrete execute plan results in a lower response ・ Issue #21912 ・ apache/doris (github.com)

Version
2.0-beta (Latest)
What's Wrong?
when executing sql query to mysql by doris catalog, I find the response speed is much lower than the way to query in mysql client. At the same time, I also use the drill engine to execute the same sql, and the response speed is 3 times faster than doris.
After that, I try to explain the sql query execute plan, I find some wrong in doris. |
such as below :
SELECT COUNT(*) FROM `xx`.`dwd__df` 
explain：
0:VJdbcScanNode                                                             |
|      TABLE: `xx`.`dwd_xx_df`                         |
|      QUERY: SELECT `ip_status` FROM `xx`.`dwd_xx_df` |

ps: why does doris scan the concrete field in the table, rather than querying count(*) directly
SELECT
  *
FROM
  `xx`.`dwd_xx_df`
WHERE
  `id` IN(
    'xxx',
    'yyyy'
  )
  AND `dayid`='20230711'
LIMIT
  20
OFFSET
  326760

explain：
SELECT FROM xx.dwd_xx_df WHERE (resource_pool_id IN ('xxx', 'yyyy')) AND (dayid = '20230711') LIMIT 326780
ps: why does not doris pick the OFFSET info, by oposite, it takes the wrong limit info . I am confused.
What You Expected?
fix the issue and improve the query speed of doris mysql catalog


cunenor@qq.com

Re:encountered some problem when using mysql catalog

Posted by Mingyu Chen <mo...@163.com>.
This is because we can not forward the origin sql directly to the MySQL server.
There are some syntax and authentication issue.


But we are working on "aggregation pushdown" feature in our new Query Optimizer: Nerieds.
After that, the `count(*)` can be pushed down to the scan node.




--

Best Regards
Mingyu Chen

Email:
morningman@apache.org





At 2023-07-18 16:59:04, "cunenor@qq.com.INVALID" <cu...@qq.com.INVALID> wrote:
>Hi Team:
>recently, when using mysql catalog,  I encountered some problem. is the way that I apply catalog wrong or something else? 
>
>does anyone can take a look at this?
>
>issue link : [Bug] when using mysql jdbc catalog, I find the unconcrete execute plan results in a lower response · Issue #21912 · apache/doris (github.com)
>
>Version
>2.0-beta (Latest)
>What's Wrong?
>when executing sql query to mysql by doris catalog, I find the response speed is much lower than the way to query in mysql client. At the same time, I also use the drill engine to execute the same sql, and the response speed is 3 times faster than doris.
>After that, I try to explain the sql query execute plan, I find some wrong in doris. |
>such as below :
>SELECT COUNT(*) FROM `xx`.`dwd__df` 
>explain：
>0:VJdbcScanNode                                                             |
>|      TABLE: `xx`.`dwd_xx_df`                         |
>|      QUERY: SELECT `ip_status` FROM `xx`.`dwd_xx_df` |
>
>ps: why does doris scan the concrete field in the table, rather than querying count(*) directly
>SELECT
>  *
>FROM
>  `xx`.`dwd_xx_df`
>WHERE
>  `id` IN(
>    'xxx',
>    'yyyy'
>  )
>  AND `dayid`='20230711'
>LIMIT
>  20
>OFFSET
>  326760
>
>explain：
>SELECT FROM xx.dwd_xx_df WHERE (resource_pool_id IN ('xxx', 'yyyy')) AND (dayid = '20230711') LIMIT 326780
>ps: why does not doris pick the OFFSET info, by oposite, it takes the wrong limit info . I am confused.
>What You Expected?
>fix the issue and improve the query speed of doris mysql catalog
>
>
>cunenor@qq.com