MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing query
Posted by: Marko Kupusic
Date: February 24, 2012 06:07AM

Hello.

I have a table with around 1 million entries. I need to query the table 2 times because it has entries with same key that need to be excluded. Problem is that when i created the query, it never finishes. When i try through mysql command line (through unix shell) it says it ran out of memory, when i do through phpmyadmin/someother_standalone_mysql_gui it stalls after around 30 minutes of waiting and never finishes.

Is there a way i could split the query somehow, or optimize better for it to execute, other then to get better hardware on my database so it maybe finishes :)

Why i need to query the database 2 times is because i need people that live in a certain city, but their living status is all kept in 1 collumn and can be "evicted" "owner" "renting" "moved", but thing is it's a long time period (there's no timestamp) so that 1 family can have both owner and moved, or owner and evicted etc (as when they move in the town their status changes to owner, but when they leave, same table adds new entry with status "moved" for example).

Query goes like this
select status,name,surname,address from database where status in ('owner','renting') and userid not in (select userid from database where status in ('evicted','moved')) and city = 'Detroit';

Any better way to query this?

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing query
1774
February 24, 2012 06:07AM
1001
February 24, 2012 01:16PM
883
February 25, 2012 03:49AM
1183
February 27, 2012 05:57AM
962
February 25, 2012 05:54PM
873
February 27, 2012 06:04AM
952
February 28, 2012 07:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.