Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What I'm trying to do is running a job on more than 100 million domains which haven't processed before.

I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm getting a list of domains from domain table by checking their status on "domain_setting". At first it was working fine but now approximately 10 million domain processed and it's getting slower.

I know that my server is crappy for this kind of database, it has only 8 GB of memory and it's also shared with Apache web server (150 concurrent connections), some other small databases and some lightweight PHP applications. My innodb_buffer_pool_size is only 1 GB.

Anyway, I would like to learn if there is a better way or thoughts to write this query more efficiently or any other approach for doing this job and speed up the process. Here is my SQL query:

SELECT * FROM domain
WHERE domain_ID NOT IN (SELECT domain_ID FROM domain_setting) OR domain_ID IN (SELECT domain_ID FROM domain_setting WHERE is_keyword_checked = 0)
LIMIT 0,10000;

I must get domains which are not exist in domain_setting table OR has "is_keyword_checked = 0" value in domain_setting table. How can I optimize this query?

Thanks in advance :)

EDIT: I can rewrite my query with LEFT JOIN as

SELECT *
FROM domain
LEFT JOIN domain_setting ON domain_setting.domain_ID = domain.domain_ID
WHERE domain_setting.is_keyword_checked = 0 OR domain_setting.is_keyword_checked IS NULL
ORDER BY domain.domain_ID ASC
LIMIT 0,10000;

but the main problem here seems full table scan. I can run it backwards by ORDER by domain.domain_ID DESC for recent added records and I can get them very fast. But still needs to scan full table if you didn't run it for a while and if recent records became more than millions of rows. So, is there any way to avoid full table scan for such work?

share|improve this question
1  
The answer depends, in part, on whether domain_ID is the primary key or is at least a unique key in domain_setting. I'm resisting the temptation to assume that it is. –  Michael - sqlbot May 10 '13 at 0:12
    
@Michael-sqlbot domain_ID is primary key for both tables. Also I have index for "is_keyword_checked" on domain_setting table. –  she hates me May 10 '13 at 7:39
    
How many possible values can the is_keyword_checked column take? Just two (0,1) or more? –  ypercube May 10 '13 at 9:24
    
@ypercube yes, only 1 and 0 –  she hates me May 10 '13 at 9:34

2 Answers 2

up vote 1 down vote accepted

Since is_keyword_checked can take only two possible values, 0 or 1 (and it's not nullable), you can try the rewriting. There is no OR and the index on (is_keyword_checked) will be used:

SELECT * 
FROM domain
WHERE domain_ID NOT IN 
      ( SELECT domain_ID 
        FROM domain_setting 
        WHERE is_keyword_checked = 1
      ) ;

or with a NOT EXISTS subquery - which should have similar execution plan:

SELECT d.* 
FROM domain AS d
WHERE NOT EXISTS 
      ( SELECT * 
        FROM domain_setting AS ds
        WHERE ds.is_keyword_checked = 1
          AND ds.domain_ID = d.domain_ID
      ) ;
share|improve this answer
    
Thank you very much, since the first query makes full table scans on both table (also because is_keyword_checked=1 data are about ten millions of rows), second query seems nearly 2x faster for now. Also it seems faster than my second query with LEFT JOIN. I can't predict what it would be when is_keyword_checked=1 data reaches 100 millions of rows but we will see :) Then probably I will only work on recent data by descend ordering domain_ID and it should be much faster. –  she hates me May 10 '13 at 11:46

If I'm reading your query correctly your asking for anything NOT in domain_setting EXCEPT for those with is_keyword_checked = 0. You should be be able to just get rid of that first not in check.

Further subqueries are not always as efficient as you might think. You could rewrite the query to be

SELECT * FROM domain join domain_settings using(domain_id) where is_keyword_checked =0

You'll want indexes on domain_id on domain. for domain_settings a compound index across (domain_id, is_keyword_checked) would be beneficial.

share|improve this answer
    
Thank you very much but your query getting only domains on "domain" has already record in domain_setting table and is_keyword_checked=0. So unfortunately it doesn't get domains which doesn't exist in domain_setting table. In default, no domain has record on domain_setting table, after processing them I add record to domain_setting table. I need to get unprocessed ones and processed ones with is_keyword_checked=0 –  she hates me May 10 '13 at 8:06
    
So my query needs anything NOT in domain_setting INCLUDING processed ones with domain_setting.is_keyword_checked = 0 –  she hates me May 10 '13 at 8:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.