When did Servoy go from generating this sql (0.34 seconds to return 7 results on 1002 customer records – Servoy 3.5.12):
select distinct customer.id_customer
from customer, address where
customer.id_customer = address.id_customer and
address.flag_mailing = 1 and
address.state like 'hi'
order by customer.name_customer asc
[attachment=0]old fast way.png[/attachment]
to generating this sql (2.65 seconds to return 7 results on 1002 customer records – Servoy 5.1.4):
select id_customer from customer where id_customer in
(select customer801.id_customer from customer customer801 left outer join address address871 on customer801.id_customer=address871.id_customer and address871.flag_mailing = 1 where address871.state like 'hi' )
order by name_customer asc
[attachment=1]new slow way.png[/attachment]
when searching on related data?
This second SQL query is 7.5x’s slower than the first query. And I can index the first query to make it a couple of orders of magnitude faster than the second query. Not sure how to index/optimize the second query. As the data sets get larger what used to be fast searches are now taking a LONG time.
Is there a good explanation for this? Any tips on optimizing? We’re running MySQL.
