Correct form long time query executing myodbc syntax

Question

I'm trying to build one SQL query for Access that links tables with myodbc connection to retrive the data from internet, but the time to finish the query is too long about five minutes, so I think the problem is with the query:

SELECT COUNT([o].[orders_id]) AS howmany_orders, 
       (SELECT SUM([op1].[products_quantity]) FROM orders_total AS ot1, orders AS o1, orders_products AS op1
        WHERE [o1].[date_purchased] >=date()-30 and [o1].[orders_id] = [op1].[orders_id] and [ot1].[orders_id] = [op1].[orders_id] and [ot1].[class]="ot_total" and [o1].[orders_status] = 1 and [op1].[products_id]=[op].[products_id]
        GROUP BY [op1].[products_id]
        ) AS pendiente,
        [op].[products_model], 
        Round((((7+1)*(howmany_orders/90))+1)-(p.stock_real- IIF(pendiente>0,pendiente,0)), 0) AS pedir,
        p.ref_id

FROM orders_total AS ot, orders AS o, orders_products AS op INNER JOIN Productos AS p ON Mid([op].[products_model],4) LIKE p.ref_id

WHERE [o].[date_purchased] >=date()-90 and [o].[orders_id] = [op].[orders_id] and [ot].[orders_id] = [op].[orders_id] and [ot].[class]="ot_total" and [o].[orders_status] IN (7, 1) and ((p.fuera_de_stock)=False) and ((p.suspendido)=False) and  ((p.quitar_de_la_web)=False)

GROUP BY [op].[products_model], p.ref_id, p.stock_real, [op].[products_id];

At a glance I see that the "LIKE" operator could be one of the problems here:

 INNER JOIN Productos AS p ON Mid([op].[products_model],4) LIKE p.ref_id

but I have not way to substitute for an = operator

Thanks for your help!

EDITING: I have reduced the query to that but is the same time:

SELECT COUNT(o.orders_id) AS howmany_orders, (
SELECT SUM(opz.products_quantity) FROM orders AS oz, orders_products AS opz WHERE oz.date_purchased >=date()-30 and oz.orders_id = opz.orders_id and oz.orders_status = 1 and opz.products_id=op.products_id GROUP BY opz.products_id
) AS pendiente, op.products_model, Round((((7+1)*(howmany_orders/90))+1)-(p.stock_real-IIf(pendiente>0,pendiente,0)),0) AS pedir, p.ref_id
FROM orders AS o, orders_products AS op INNER JOIN Productos AS p ON op.products_model=p.cod
WHERE o.date_purchased>=date()-90 And o.orders_id=op.orders_id And o.orders_status In (7,1) And ((p.suspendido)=False) And ((p.quitar_de_la_web)=False)
GROUP BY op.products_model, p.ref_id, p.stock_real, op.products_id;

Answer 1

Yep there’s your problem. The database can not do the join using any indexes so it has to do a table scan. Is there anyway you could persist this data so you don’t have to do the MID statement and just join on that? i.e. in your products_model table have an extra column with the MID data stored in there with the join on that column