Optimize SQL

Question

kishan112 0 Light Poster

15 Years Ago

Hi, can anyone please rewrite the following sql-query?
It's working, but it takes some time to execute.

SELECT `cd_e_calls`.`bedrijfs_id` AS gegevens_id, `gebeld` , `status` 
FROM `cd_e_calls` , `cd_e_gegevens` , `cd_e_negatief` 
WHERE `cd_e_gegevens`.`RvV` != '1'
AND `bedrijfs_id` = ( 
    SELECT `cd_e_gegevens`.`id` AS gegevens_id
    FROM `cd_e_gegevens` 
    WHERE (

          SELECT COUNT( `cd_e_calls`.`bedrijfs_id` ) 
          FROM `cd_e_calls` 
          WHERE `cd_e_calls`.`bedrijfs_id` = `cd_e_gegevens`.`id` ) =2
     AND (

          SELECT MAX( `cd_e_calls`.`gebeld` ) 
          FROM `cd_e_calls` 
          WHERE `cd_e_calls`.`bedrijfs_id` = `cd_e_gegevens`.`id` 
) NOT LIKE '2009-08-25'
     ORDER BY `id` DESC 
     LIMIT 1 
)
AND `cd_e_negatief`.`gegevens_id` != `cd_e_gegevens`.`id` 
LIMIT 1

I normally speak Dutch, but I don't think that really matters :)

mysql

3 Contributors
16 Replies
161 Views
1 Day Discussion Span
Latest Post 15 Years Ago Latest Post by kishan112

ferensick 23 Newbie Poster

15 Years Ago

Hi kishan112,
Which storage engine are you using (MyISAM, InnoDB)? And how many records are expected to be returned?
Out of curiosity how are you executing the query? (via phpMyAdmin, MySQL Administrator)?

ferensick 23 Newbie Poster

15 Years Ago

-- 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 a lot of "id's" (or maybe that is something else in Dutch... I'm not sure). But what would really help to optimize the execution time is indexing. Any columns used in the WHERE clause should be indexed for better performance. (Again depending on # of records). If there's a lot of records the query has to go through, then indexing will improve performance. I don't know how much access you have to the MySQL database or where it's hosted... but hopefully you can find out what columns are indexed. You can also execute this query to see the indexes yourself: SHOW CREATE TABLE cd_e_calls;
If you see no indexing on any of the tables. This is the core issue. If there is indexing, reply back. I am at work now and can not spend much more time today on this. But I will have more time tomorrow or later today to look at this closer. *If there is indexing then you can use hints as well: http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

iamthwee commented: That's some pretty tight advice +23

ferensick 23 Newbie Poster

15 Years Ago

*thanks I can see how many records there is now. =) Indexing could be useful.

ferensick 23 Newbie Poster

15 Years Ago

Yes you can do a join. I am working on it now. Also even if MyISAM is the default storage engine you can still change the table's storage engine; ALTER TABLE (table name) ENGINE=InnoDB;
You can try this with your existing query out of curiosity. In the meantime I am working on an altered query for the above. =)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kishan112 0 Light Poster · Answer 1 · 2009-08-26T01:05:05+00:00

Can anyone please help me?
This is urgent :(
If you want more information please tell me..

kishan112 0 Light Poster · Answer 2 · 2009-08-26T01:55:49+00:00

I don't know which enginge I'm using.. I'm using xampp.
De query is executed by phpMyAdmin. And I just expect 1 records.
This query works, but it takes a long time for the result.
The table `cd_e_calls` contains 2240 rows, `cd_e_gegevens` contains 5000 rows and `cd_e_negatief` contains 450 rows

kishan112 0 Light Poster · Answer 3 · 2009-08-26T04:47:24+00:00

It still takes the same time to execute.
"Showing rows 0 - 0 (1 total, Query took 20.0271 sec)"
This was after I removed the first LIMIT 1. Actually this LIMIT 1 is necessary, because of this:
cd_e_calls.bedrijfs_id = result of sub query with limit 1

id is the same id in english.
In my php application I'm adding a row in cd_e_calls everytime when I receive a result from this query.
I have full access to the database and the host and the server.
Can't I use JOIN here?

I'll try to tell what I want with this query.
There are 6 tables:
cd_e_gegevens (containing some records with the information of some persons)
cd_e_calls (registers calls to persons in the cd_e_gegevens table)
cd_e_negatief (negative calls, don't call again, that's why there is AND cd_e_negatief.gegevens_id != cd_e_gegevens.id
cd_e_opmerking (stores some notes of the call - not important)
cd_e_positief (stores the positive calls, actually there have to be AND cd_e_positief.gegevens_id != cd_e_gegevens.id to
cd_e_terugbel (stores when to call someone again - scheduled time)

1 Now first I look if there is a scheduled call (cd_e_terugbel)

SELECT `$t_gegevens`.`id` as gegevens_id, `$t_terug`.`id` as terugbel_id FROM `$t_terug` `$t_gegevens`,`$t_calls` WHERE `$t_terugbel`.`call_id`=`$t_calls`.`id` AND `$t_calls`.`bedrijfs_id`=`$t_gegevens`.`id` AND `$t_terug`.`datum` < '$nu' AND `$t_calls`.`agent`='$agent' LIMIT 1

2 After that I look if there is a person (in cd_e_gegevens) which haven't been called (no record in cd_e_calls)

SELECT `$t_gegevens`.`id` as gegevens_id FROM `$t_gegevens` WHERE (SELECT COUNT(`$t_calls`.`bedrijfs_id`) FROM `$t_calls` WHERE `$t_calls`.`bedrijfs_id`=`$t_gegevens`.`id`) = 0 LIMIT 1

3 After that I look for a person who've been called 1 time (1 record in cd_e_calls)

SELECT `$t_calls`.`bedrijfs_id` as gegevens_id,`gebeld`,`status` FROM `$t_calls` WHERE `bedrijfs_id`=(SELECT `$t_gegevens`.`id` as gegevens_id FROM `$t_gegevens` WHERE (SELECT COUNT(`$t_calls`.`bedrijfs_id`) FROM `$t_calls` WHERE `$t_calls`.`bedrijfs_id`=`$t_gegevens`.`id`) = 1 AND (SELECT MAX(`$t_calls`.`gebeld`) FROM `$t_calls` WHERE `$t_calls`.`bedrijfs_id`=`$t_gegevens`.`id`) NOT LIKE '$vandaag%' ORDER BY `id` DESC LIMIT 1)

4 The above query is when looking for a person that've been called 2 times (2 records in cd_e_calls).

SELECT `$t_calls`.`bedrijfs_id` as gegevens_id,`gebeld`,`status` FROM `$t_calls` WHERE `bedrijfs_id`=(SELECT `$t_gegevens`.`id` as gegevens_id FROM `$t_gegevens` WHERE (SELECT COUNT(`$t_calls`.`bedrijfs_id`) FROM `$t_calls` WHERE `$t_calls`.`bedrijfs_id`=`$t_gegevens`.`id`) = 2 AND (SELECT MAX(`$t_calls`.`gebeld`) FROM `$t_calls` WHERE `$t_calls`.`bedrijfs_id`=`$t_gegevens`.`id`) NOT LIKE '$vandaag%' ORDER BY `id` DESC LIMIT 1) LIMIT 1

Now even in criteria 1 it takes a long time.

I'm sorry, my english is not so good...

vincent2085 -32 Junior Poster in Training · Answer 4 · 2009-08-26T08:37:34+00:00

Which storage engine are you using (MyISAM, InnoDB)? And how many records are expected to be returned?

kishan112 0 Light Poster · Answer 5 · 2009-08-26T08:40:11+00:00

Which storage engine are you using (MyISAM, InnoDB)? And how many records are expected to be returned?

How can I see the engine? Is there a special command?
Only 1 record have to be returned

kishan112 0 Light Poster · Answer 6 · 2009-08-26T09:00:50+00:00

InnoDB has been disabled for this MySQL server.
MyISAM is the default storage engine on this MySQL server.

ferensick 23 Newbie Poster · Answer 7 · 2009-08-26T19:57:19+00:00

/* Solution 1: I am sorry I can not test this or play with it... please test and let me know the results and if it's the desired results */
------

[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] bedrijfs_id = 
	(
			[B]SELECT [/B]
			cd_e_gegevens.id AS gegevens_id,
			[B]COUNT[/B](cd_e_calls.bedrijfs_id), [B]MAX[/B](cd_e_calls.gebeld)
			[B]FROM[/B] cd_e_gegevens
			[B]JOIN[/B] cd_e_calls ON cd_e_gegevens.? = cd_e_calls.?
			[B]JOIN[/B] cd_e_negatief ON cd_e_negatief.gegevens_id != cd_e_gegevens.id
			[B]AND[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
			[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
			[B]HAVING COUNT[/B](cd_e_calls.bedrijfs_id) = 2
			[B]HAVING MAX[/B](cd_e_calls.gebeld) NOT LIKE '2009-08-25'	
			[B]GROUP BY[/B] cd_e_gegevens.id
			[B]ORDER BY[/B] id [B]DESC[/B]
			LIMIT 1
	)

ferensick 23 Newbie Poster · Answer 8 · 2009-08-26T20:03:40+00:00

/* Solution 2 --again I can not test... please do so and tell me results =) */
--2

[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] 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]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]HAVING COUNT[/B](cd_e_calls.bedrijfs_id) =2
			[B]HAVING MAX[/B]( cd_e_calls.gebeld ) NOT LIKE '2009-08-25'
		)
		[B]ORDER BY[/B] id [B]DESC[/B]
		[B]LIMIT[/B] 1
)
[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
[B]LIMIT[/B] 1

kishan112 0 Light Poster · Answer 9 · 2009-08-26T20:31:12+00:00

Solution 1:

SELECT cd_e_calls.bedrijfs_id AS gegevens_id, gebeld , status
FROM cd_e_calls , cd_e_gegevens , cd_e_negatief
WHERE cd_e_gegevens.RvV != '1'
AND bedrijfs_id = 
(
SELECT 
cd_e_gegevens.id AS gegevens_id,
COUNT(cd_e_calls.bedrijfs_id), MAX(cd_e_calls.gebeld)
FROM cd_e_gegevens
JOIN cd_e_calls ON cd_e_gegevens.id = cd_e_calls.bedrijfs_id
JOIN cd_e_negatief ON cd_e_negatief.gegevens_id != cd_e_gegevens.id
AND cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
AND cd_e_negatief.gegevens_id != cd_e_gegevens.id
HAVING COUNT(cd_e_calls.bedrijfs_id) = 2
HAVING MAX(cd_e_calls.gebeld) NOT LIKE '2009-08-25' 
GROUP BY cd_e_gegevens.id
ORDER BY id DESC
LIMIT 1
)

I receive te following error:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING MAX( cd_e_calls . gebeld ) NOT LIKE '2009-08-25' GROUP BY cd_e_gegevens ' at line 1

After changing the second HAVING with AND, I receive the following:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY cd_e_gegevens . id ORDER BY id DESC LIMIT 1 )
LIMIT 0, 30' at line 1

I can't figure that out..

Solution2:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING MAX( cd_e_calls.gebeld ) NOT LIKE '2009-08-25'
)
ORDER BY id DESC
LIMI' at line 14

After changing the second HAVING to AND:
MySQL said:

#1241 - Operand should contain 1 column(s)

kishan112 0 Light Poster · Answer 10 · 2009-08-26T20:34:07+00:00

These are my tables

CREATE TABLE `cd_e_calls` (
  `id` int(11) NOT NULL auto_increment,
  `bedrijfs_id` int(11) NOT NULL,
  `gebeld` datetime NOT NULL,
  `afgebeld` datetime NOT NULL,
  `status` varchar(4) collate latin1_general_ci NOT NULL,
  `werkplek` int(2) NOT NULL,
  `opmerking` text collate latin1_general_ci NOT NULL,
  `agent` varchar(20) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3156 ;

CREATE TABLE `cd_e_gegevens` (
  `id` int(11) NOT NULL auto_increment,
  `abonr` varchar(10) collate latin1_general_ci NOT NULL,
  `aanhef` varchar(5) collate latin1_general_ci NOT NULL,
  `naam` varchar(30) collate latin1_general_ci NOT NULL,
  `adres` varchar(40) collate latin1_general_ci NOT NULL,
  `postcode` varchar(8) collate latin1_general_ci NOT NULL,
  `plaats` varchar(30) collate latin1_general_ci NOT NULL,
  `telefoon` varchar(15) collate latin1_general_ci NOT NULL,
  `smartcard_oud` varchar(18) collate latin1_general_ci NOT NULL,
  `smartcard` varchar(18) collate latin1_general_ci NOT NULL,
  `email` varchar(35) collate latin1_general_ci NOT NULL,
  `banknr` varchar(12) collate latin1_general_ci NOT NULL,
  `pakket` varchar(50) collate latin1_general_ci NOT NULL,
  `RvV` int(1) NOT NULL,
  `bestand` varchar(2) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5001 ;

CREATE TABLE `cd_e_negatief` (
  `id` int(11) NOT NULL auto_increment,
  `gegevens_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=644 ;

ferensick 23 Newbie Poster · Answer 11 · 2009-08-27T01:58:29+00:00

Solution 1:
SELECT cd_e_calls.bedrijfs_id AS gegevens_id, gebeld , status
FROM cd_e_calls , cd_e_gegevens , cd_e_negatief
WHERE cd_e_gegevens.RvV != '1'
AND bedrijfs_id = 
(
SELECT 
cd_e_gegevens.id AS gegevens_id,
COUNT(cd_e_calls.bedrijfs_id), MAX(cd_e_calls.gebeld)
FROM cd_e_gegevens
JOIN cd_e_calls ON cd_e_gegevens.id = cd_e_calls.bedrijfs_id
JOIN cd_e_negatief ON cd_e_negatief.gegevens_id != cd_e_gegevens.id
AND cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
AND cd_e_negatief.gegevens_id != cd_e_gegevens.id
HAVING COUNT(cd_e_calls.bedrijfs_id) = 2
HAVING MAX(cd_e_calls.gebeld) NOT LIKE '2009-08-25' 
GROUP BY cd_e_gegevens.id
ORDER BY id DESC
LIMIT 1
)
I receive te following error:
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING MAX( cd_e_calls . gebeld ) NOT LIKE '2009-08-25' GROUP BY cd_e_gegevens ' at line 1
After changing the second HAVING with AND, I receive the following:
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY cd_e_gegevens . id ORDER BY id DESC LIMIT 1 )
LIMIT 0, 30' at line 1
I can't figure that out..
Solution2:
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING MAX( cd_e_calls.gebeld ) NOT LIKE '2009-08-25'
)
ORDER BY id DESC
LIMI' at line 14
After changing the second HAVING to AND:
MySQL said:
#1241 - Operand should contain 1 column(s)

Hey,
thanks for telling me the results.
Instead of using HAVING NOT LIKE '...'
Try using HAVING NOT IN ('date')
Or try using HAVING <> ''
*Did you try solution 2 as well?

kishan112 0 Light Poster · Answer 12 · 2009-08-27T02:12:49+00:00

Stil getting this with solution 1:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY cd_e_gegevens . id ORDER BY id DESC LIMIT 1 )

Solution 2 has the following error:
MySQL said:

#1241 - Operand should contain 1 column(s)