Suppose I have numbers from 1 to 100 in a table.
I need to write a query to extract all the prime numbers from this table. how can I achieve this with a very basic and simple query without using any sort of procedures or loops.
MyISAM was chosen for a reason. I will explain in comments. Mainly to guarantee a no innodb gap anomoly during self-inserts (thus throwing off the id's). Don't look into the schema part of it too much. I just needed to generate a table 1 to 100.
Anyway, had you provided the actual table I would not need to mention that. Or an
ALTER TABLE can change the engine after the data load.
create table nums ( id int auto_increment primary key, thing char(1) null )ENGINE=MyISAM; insert nums(thing) values(null),(null),(null),(null),(null),(null),(null); insert nums(thing) select thing from nums; insert nums(thing) select thing from nums; insert nums(thing) select thing from nums; insert nums(thing) select thing from nums; select count(*) from nums; -- 112 delete from nums where id>100; select min(id),max(id),count(*) from nums; -- 1 100 100
select id from nums where id>1 and id not in ( select distinct n2id from ( select n1.id as n1id, n2.id as n2id from nums n1 cross join nums n2 where n1.id<(n2.id) and n1.id>1 and (n2.id MOD n1.id = 0) ) xDerived ) order by id;
+----+ | id | +----+ | 2 | | 3 | | 5 | | 7 | | 11 | | 13 | | 17 | | 19 | | 23 | | 29 | | 31 | | 37 | | 41 | | 43 | | 47 | | 53 | | 59 | | 61 | | 67 | | 71 | | 73 | | 79 | | 83 | | 89 | | 97 | +----+ 25 rows in set (0.00 sec)
Note, ref2 above is an exaggerated "quickly create a 4.7M row table" that would have definitely create INNODB id gaps if not done that way. It is just a known fact with that engine.