Re: tables, joins or not, to script or not
Posted by:
Rick James
Date: March 20, 2010 01:16PM
Please give me some specifics to work from:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
I am having trouble following the verbal description, such as
* what "chart" refers to;
* whether clusterid is an INT (preferably) or a string like "13, 21, 29, 32, 34..." (preferably not)
* "OR .... 21" means clusterid? Consider "clusterid IN (13, 21, 29, ...)"
* Random other terms: group, meeting, set, member, ingredient, name, cluster.
JOINs and subqueries are ways to take the results from querying one table and using it against another table. JOIN tends to be the most efficient, but not necessarily the most obvious. Another technique is to CREATE TEMPORARY TABLE to store the 'intermediate' results from one table before JOINing to another.
Some subquery constructs:
WHERE id IN ( SELECT id FROM foo WHERE ... )
FROM ( SELECT a, b FROM foo WHERE ... )
Both of those can _usually_ be turned into a JOIN. However, if the subquery contains GROUP BY or LIMIT, it is _usually_ better (sometimes even necessary) to use the subquery form.