dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: [RESOLVED] SQL: My query works, but is very slow (finding items that are not in a table)

  1. #1
    Join Date
    May 2006
    Location
    Dresden, Germany
    Posts
    458

    [RESOLVED] SQL: My query works, but is very slow (finding items that are not in a table)

    Hi,

    I'm a program artist but not an expert in SQL.

    My problem is to get items from a table which are not referenced in another table.

    Using MySQL I created a query like that:
    PHP Code:
    SELECT FROM `main_tableWHERE ((SELECT `idFROM ref_table WHERE ref_table.id=main_table.id AND ref_table.user_id='me'IS NULL
    This works fine: I get all records from the main_table which are not referenced in the ref_table. But it is very slow. In my example MySQL needed more then 2 minutes to calculate the (correct) records.

    I' m (almost) sure that there is a way to get this list faster.

    Maybe you want me to change my database design. I do not really want to do that since I have to change all the other queries too. But if this is my only chance let me know. In most cases I do need the other way: Searching for records that are referenced (which is really fast).

    Can anyone suggest something?

    With regards
    Programartist

  2. #2
    Join Date
    May 2006
    Location
    Dresden, Germany
    Posts
    458

    Thumbs up Re: SQL: My query works, but is very slow (finding items that are not in a table)

    Hi,

    I solved this by first googling around and by second adding indizes to the ref_table. It is now as fast as all the other queries.

    With regards
    Programartist

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)