Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Sub SELECT Performance

    Hi everybody,

    Im using a double SUB SELECT Statement to filter result sets according to Data in two different Linked tables. The Statement looks like that:

    SELECT field1 FROM table1 WHERE field1 IN (SELECT field2 FROM table2 WHERE field2 IN (SELECT field3 FROM table 3))

    Ths works, using MySQL Version 4.1. The problem ist, that the performance of this statement is extremely bad. A Statement like this, having round 150 to 300 records per mentioned table, takes about 40 seconds, wich is quite useless.

    Im very thankful for any kind of hint on this. Maybe someone made a similar experience...

    Thanks in advance,

    Babbit

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Sub SELECT Performance

    Originally posted by babbit
    Hi everybody,

    Im using a double SUB SELECT Statement to filter result sets according to Data in two different Linked tables. The Statement looks like that:

    SELECT field1 FROM table1 WHERE field1 IN (SELECT field2 FROM table2 WHERE field2 IN (SELECT field3 FROM table 3))

    Ths works, using MySQL Version 4.1. The problem ist, that the performance of this statement is extremely bad. A Statement like this, having round 150 to 300 records per mentioned table, takes about 40 seconds, wich is quite useless.

    Im very thankful for any kind of hint on this. Maybe someone made a similar experience...

    Thanks in advance,

    Babbit
    Well, having a SQL like this one is looking for trouble... Rethink your query and use JOINs !!

    But anyway, what does the EXPLAIn on this query tell you?

    And can you provide a DESCRIBE of the involved tables as well? Then we'll be able to pinpoint the problem...

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Further Problems regarding ODBC

    Hi,

    first of all, thanks for replying. I got the problem fixed now by downgrading to MySQL 4.0 and rewriting SUB-Selects using JOIN (which was a considerable amount of work :-/).

    Anyway, while going further in migrating all my webs to MySQL, I encountered another problem. Now Im using MySQL 4.0 and ODBC Driver Version 3.51 (which is the most recent on, as far as I could find out). Scripting platform is ASP.
    When checking TEXT or LONGTEXT type fields as to if they are empty ("") or NULL, I get an error when trying to print their content to screen. The code looks like that:

    If Not (rs.fields("whatever") = "" OR isnull(rs.fields("whatever"))) then
    Response.Write(rs.fields("whatever"))
    End If

    This causes a "Microsoft OLE DB Provider for ODBC Drivers-Error '80020009' "

    I have tried to get rid of this or find a solution for hours. But with no success. Probably someone can help me in this, please...

    Thanks in advance for any posts,

    Babbit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •