Posts by ferensick

-- original query

[B]SELECT [/B]
cd_e_calls.bedrijfs_id as gegevens_id, gebeld, status
[B]FROM[/B] cd_e_calls, cd_e_gegevens, cd_e_negatief
[B]WHERE[/B] cd_e_gegevens.RvV != '1'
[B]AND[/B] cd_e_calls.bedrijfs_id = 
(
	[B]SELECT[/B] cd_e_gegevens.id as gegevens_id
	[B]FROM[/B] cd_e_gegevens
	[B]WHERE[/B] 
		(
			[B]SELECT COUNT[/B](cd_e_calls.bedrijfs_id)
			[B]FROM[/B] cd_e_calls
			[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
		) =2
			[B]AND[/B] 
		(
			[B]SELECT MAX[/B](cd_e_calls.gebeld)
			[B]FROM[/B] cd_e_calls
			[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id
		) [B]NOT LIKE[/B] '2009-08-25'
		[B]ORDER BY[/B] id [B]DESC[/B]
		[B]LIMIT[/B] 1 -- is this necessary? I would take it out and try it again, since you have LIMIT 1 already at the end
)
[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
[B]LIMIT[/B] 1

-------------------------
Other option, turn your subquery into a view:

[B]CREATE OR REPLACE ALGORITHM[/B] = TEMPTABLE [B]VIEW [/B][U] v_cd_e_GCB[/U] [B]AS[/B]
[B]SELECT[/B] cd_e_gegevens.id as gegevens_id
[B]FROM[/B] cd_e_gegevens
[B]WHERE[/B] 
	(
		[B]SELECT COUNT[/B](cd_e_calls.bedrijfs_id)
		[B]FROM[/B] cd_e_calls
		[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
	) =2
		[B]AND[/B] 
	(
		[B]SELECT MAX[/B](cd_e_calls.gebeld)
		[B]FROM[/B] cd_e_calls
		[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id
	) [B]NOT LIKE[/B] '2009-08-25'
[B]ORDER BY[/B] id DESC
[B]LIMIT[/B] 1 -- if you want the view to be re-usable, take this limitation out, it will be more scalable

-------------------------
New Query with view:

[B]SELECT [/B]
cd_e_calls.bedrijfs_id as gegevens_id, gebeld, status
[B]FROM[/B] cd_e_calls, cd_e_gegevens, cd_e_negatief
[B]WHERE[/B] cd_e_gegevens.RvV != '1'
[B]AND[/B] cd_e_calls.bedrijfs_id = 
(
SELECT * FROM [U]v_cd_e_GCB[/U] LIMIT 1
)
[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
[B]LIMIT[/B] 1

-------------------------
What I think the real problem is, the database set up. MySQL is great at optimizing queries as they are. I have done a few tests of my own with different queries (same results) with execution time barely changing. I noticed that your selecting …