i don't know why this query take 45 min to give me the result
table1= 831293 row
table2= 2000.000 row
SELECT ID, name FROM table1 WHERE name not IN (SELECT name FROM table2 ) LIMIT 831293
my.cnf file look like this :
max_allowed_packet = 64M
innodb_buffer_pool_size = 8G
innodb_log_file_size = 256M
query_cache_type = 1
query_cache_limit = 1M
Do you have an index on name?
If you have to loop each time through table2 per row, you effectively loop through 831293*200000 rows, which is a lot.
When you have an index on name, you can get a major performance increase, as it is easier to search for a name in this index. (probably even O(1) as there will probably be a hashtable)
you can do this as follows
ALTER TABLE `table2` ADD INDEX (`name`)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments