MySQL Performance – Indexing 2 Million Rows with LONGTEXT and VARCHAR

This is definitely a loaded question. Why ???

• InnoDB caches data and index pages
• InnoDB may lock Clustered Indexes during Inserts/Updates
• InnoDB is multithreaded and can be tuned for accessing CPUs/Cores
• MyISAM only caches index pages
• MyISAM must always be read data from disk
• MyISAM can have dedicated caches per tables

If you have plenty of RAM and intend to read the same data throughout a business day, some would go with InnoDB. If you are looking for high-speed reads from varying ranges of data, some would go with MyISAM. Even still, there are simply too many mitigating factors to pick one fully over the other such as

• VARCHAR field processing
• Row Format
• Foreign Keys
• Index Cardinality
• Physical Disk (SSD, SATA, SAN, NAS)
• Application Processing Needs

Here are some posts that compare and contrast MyISAM and InnoDB

• Sep 20, 2011 : Best of MyISAM and InnoDB
• April 12, 2012 : Mysql: Insert performance INNODB vs MYISAM (Not My Post, but good answers are given)
• May 02, 2012 : Which mysql storage engine to choose?
• May 03, 2012 : Which is faster, InnoDB or MyISAM?
• Aug 17, 2012 : When to switch from MyISAM to InnoDB?

Please read these before deciding ...

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
      ) ;