CODEWITHSUNDEEP
mysqlsqlmysql-workbench
HerrimanCoder
HerrimanCoder

Reputation: 7228

Why does MySQL Workbench execute a query so much slower than my app?

I have an app that runs a complex join and it returns in a few seconds. No big deal. But when I run that same exact query in Workbench 8.0.12, it takes over a minute. The connection string is identical.

This post doesn't help, seems to be a different issue.

My app is .NET using MySQL.Data dll. I have used Workbench many times in the past, but usually with PHP apps. From years of experience I have always seen Workbench run a query in roughly the same time as the app can run it.

Here is my query:

SELECT 
    model.Code as ModelCode, 
    prod.parts_group as PartsGroup, 
    rules.id AS ID, 
    rules.Code as ConstraintName, 
    REPLACE(REPLACE(REPLACE(REPLACE(rules.statement, '''', ''), ' me.', ''), '}and ', '} and '), '}or ', '} or ') as Statement, 
    ifnull(rules.priority,5) AS Priority, 
    model.serie_id AS SeriesID
FROM 
    toy_product_constraint as rules
    INNER JOIN toy_product as prod ON rules.product_id = prod.id
    INNER JOIN toy_product_model as model ON prod.model_id = model.id
    JOIN ModelImport mi ON model.code = mi.ModelCode
WHERE
    rules.statement <> 'TRUE'
    AND rules.market_id is null
    AND rules.statement not like 'hasSDR(true)%'
    AND rules.statement like 'SPECOPTION{00%'

The last 3 where clauses seem to be adding 99% of the extra query time:

AND rules.market_id is null
AND rules.statement not like 'hasSDR(true)%'
AND rules.statement like 'SPECOPTION{00%'

Without those last 3, the query runs in 1 second in Workbench. With those last 3 it takes over 60 seconds in Workbench. We could talk about indexes and other things, but the real mystery is why does this same query run (and return records) in less than 3 seconds in my app (I have stepped through the code line-by-line and verified this) and over 60 seconds in workbench??

Upvotes: 2

Views: 1843

Answers (1)

HerrimanCoder
HerrimanCoder

Reputation: 7228

I found the answer: The built-in Limit to 500 rows, etc, seems to be REALLY slow to figure out which 500 to do. When I open it wide, it runs fast. It's a large dataset.

Upvotes: 2

Related Questions