-
March 23rd, 2010, 07:46 AM
#1
[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_table` WHERE ((SELECT `id` FROM 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
-
March 25th, 2010, 03:46 AM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|