Ok, I got downvoted on this, so I decided to test it: CREATE TABLE userrole ( userid INT, roleid INT, PRIMARY KEY (userid, roleid) ); CREATE INDEX ON userrole (roleid); Run this: <?php ini_set(‘max_execution_time’, 120); // takes over a minute to insert 500k+ records $start = microtime(true); echo “<pre>\n”; mysql_connect(‘localhost’, ‘scratch’, ‘scratch’); if (mysql_error()) { … Read more
How about this? SELECT carname FROM PassedTest GROUP BY carname HAVING COUNT(DISTINCT testtype) = 4 You can also use it as an inner statement for taking info from the cars table: SELECT * FROM cars WHERE carname IN ( SELECT carname FROM PassedTest GROUP BY carname HAVING COUNT(DISTINCT testtype) = 4 )
You can either use GROUP BY and HAVING COUNT(*) = _: SELECT contact_id FROM your_table WHERE flag IN (‘Volunteer’, ‘Uploaded’, …) GROUP BY contact_id HAVING COUNT(*) = 2 — // must match number in the WHERE flag IN (…) list (assuming contact_id, flag is unique). Or use joins: SELECT T1.contact_id FROM your_table T1 JOIN your_table … Read more
I was curious. And as we all know, curiosity has a reputation for killing cats. So, which is the fastest way to skin a cat? The cat-skinning environment for this test: PostgreSQL 9.0 on Debian Squeeze with decent RAM and settings. 6.000 students, 24.000 club memberships (data copied from a similar database with real life … Read more