How can I fix this sql query?

Question

While executing below query facing "Lost connection to MySQL server during query" error. how can I fix it?

SELECT * 
FROM firmalar,
     musterisahipleri,
     notlar 
WHERE firmalar.firmaID NOT IN (
       SELECT m2.firmaID 
       FROM notlar AS n2,
            musterisahipleri AS m2
       WHERE n2.eklemeTarihi > '2013-03-24'
   ) 
   AND musterisahipleri.firmaID = firmalar.firmaID 
   AND notlar.ilgiliID = musterisahipleri.userID;

Thanks for your help

The error looks to me like a timeout. You seem to have a HUGE join in your subquery, perhaps if you sort that out the whole thing will complete faster? — nurdglaw, May 25 '13 at 11:51
This question is very vague... what kind of data is contained within your database? Seems like your query is processing a large dataset and times out? Have a look at this question: http://stackoverflow.com/questions/1884859/lost-connection-to-mysql-server-during-query — Filippos Karapetis, May 25 '13 at 11:52
As others have said, likely a time out. Hard to see where though from waht you have posted. The table structures (or relevant bits), any indexes, and an Explain might enable us to help you. Along with using proper syntax.. — Tony Hopkinson, May 25 '13 at 11:55

Steven Moseley · Accepted Answer · 2013-05-25T12:43:14.283

5

You're timing out because you're using inefficient nested subqueries.

This will perform better:

EDIT: Per your last comment, this query will return you firmalar records that have no notlar records added since '2013-03-24'... it then joins those results on musterisahipleri and notlar again to get associated reps and notes (if applicable)

SELECT *
FROM (
    SELECT f.* 
    FROM firmalar AS f
    LEFT JOIN musterisahipleri AS m
        ON m.firmaID = f.firmaID
    LEFT JOIN notlar AS n
        ON n.ilgiliID = m.userID
        AND n.eklemeTarihi > '2013-03-24'
    GROUP BY f.firmaID
    HAVING MAX(n.ilgiliID) IS NULL
) AS f
    LEFT JOIN musterisahipleri AS m
        ON m.firmaID = f.firmaID
    LEFT JOIN notlar AS n
        ON n.ilgiliID = m.userID

You should also ensure you have indexes on the columns you're joining on, e.g.

ALTER TABLE firmalar ADD INDEX (firmaID);
ALTER TABLE musterisahipleri ADD INDEX (firmaID);
ALTER TABLE musterisahipleri ADD INDEX (userID);
ALTER TABLE notlar ADD INDEX (ilgiliID);

edited May 25 '13 at 12:43

answered May 25 '13 at 11:54

Steven Moseley

15,871
4
39
50

2

Swift, I was just typing up something like this. – Tony Hopkinson May 25 '13 at 11:59
Thanks but with this query I got Null "notlar" – Ugur Ozer May 25 '13 at 12:05
This is not the same query as the original one. The original one excludes all results with a date greater than '2013-03-24'. In your query, you're doing the inverse – Filippos Karapetis May 25 '13 at 12:07
magmedya.net/2.png magmedya.net/3.png – Ugur Ozer May 25 '13 at 12:07
@UgurOzer - I edited the query in my answer. Have another go? – Steven Moseley May 25 '13 at 12:08
@FilipposKarapetis - Yeah, I noticed that. The original query was packed into one line, which made it hard to read. – Steven Moseley May 25 '13 at 12:08
Actually I need to list "firmalar" which one not added at least 2 months. With this list I need to add latest notes of this "firmalar" called "notlar" with "musterisahipleri". With my English I think it became a little bit complicated. sorry. – Ugur Ozer May 25 '13 at 12:09
You need the firma, the musterisahipleri, and the notlar? what is the musterisahipleri? Does each table have a priimary key? If so, what are they called? Thanks. – Steven Moseley May 25 '13 at 12:14
Ok I'll try to explain like that. I have 3 tables. Firma (Companies), musterisahipleri (Representives) and Notlar (notes). you can find all these tables at http://magmedya.net/1.png - http://magmedya.net/2.png and http://magmedya.net/3.png. So I'm trying to list Notlar (notes) which one not added on last 2 months. And I want to add Firmalar (Companies) and their representives (Musterisahipleri) to this list. – Ugur Ozer May 25 '13 at 12:17
Ok, that's a little more complex. Give me a sec. – Steven Moseley May 25 '13 at 12:18
eklemeTarihi means notes' add date in musterisahipleri Table – Ugur Ozer May 25 '13 at 12:19
Surely. Thanks a lot Steven. I'm waiting for 15 rep. to vote up your answer :) – Ugur Ozer May 25 '13 at 12:21
It comes with this error "#1248 - Every derived table must have its own alias" – Ugur Ozer May 25 '13 at 12:23
Yup, added the alias (oops!) – Steven Moseley May 25 '13 at 12:26
I know it started be disturbing but it comes with this error this time. "#1054 - Unknown column 'n.ilgiliID' in 'on clause'" – Ugur Ozer May 25 '13 at 12:28
Make sure you got my latest edit. I had incorrectly aliased notlar as `n2` in one of my edits. – Steven Moseley May 25 '13 at 12:29
I really shame of my lack of knowledge but it is like that. http://magmedya.net/4.png Isn't it your latest edit? – Ugur Ozer May 25 '13 at 12:38
Yes, that's my latest edit. But now I'm confused. Isn't there an `ilgiliID` on the `notlar` table? – Steven Moseley May 25 '13 at 12:42
That's really odd. Try running just the inner query first, to see if the error is there or on the outer query. `SELECT f.* FROM firmalar AS f LEFT JOIN musterisahipleri AS m ON m.firmaID = f.firmaID LEFT JOIN notlar AS n ON n.ilgiliID = m.userID AND n.eklemeTarihi > '2013-03-24' GROUP BY f.firmaID HAVING n.ilgiliID IS NULL` – Steven Moseley May 25 '13 at 12:44
PS - note a new addition of a `MAX()` function in the `HAVING` clause – Steven Moseley May 25 '13 at 12:45
Still same :( I think there is a problem about me. Really thanks a lot. You don't have to pore upon this problem anymore... – Ugur Ozer May 25 '13 at 12:50

score 1 · Answer 2 · answered May 25 '13 at 13:09

You can speed up not in subqueries in older versions by MySQL by replacing them with not exists. The appropriate indexes definitely help (as recommended by Steven Moseley.

This version moves the joins into the from clause and replaces the not in with not exists:

SELECT * 
FROM firmalar join
     musterisahipleri 
     on musterisahipleri.firmaID = firmalar.firmaID join
     notlar 
     on notlar.ilgiliID = musterisahipleri.userID
WHERE not exists (select 1
                  FROM notlar n2 join
                       musterisahipleri m2
                       on n2.ilgiliID = m3.userID
                 WHERE n2.eklemeTarihi > '2013-03-24' and
                       firmalar.firmaID = m2.firmaID
                )

In writing this, I realize that the problem with the original query is that the tables in the not in subquery were not properly joined together. This yet again emphasizes why proper join syntax (using the join and on keywords in the from clause) is superior to implicit joins in the where clause.

How can I fix this sql query?

2 Answers2