I'm working with a particularly large database, and am not particularly in tune with some of the intricacies of RDBMS's as I work primarily in PHP/WP and with smaller databases. Either way, I have created a table of data based on the aggregate combination of four other tables with about 100 rows each, producing about 122m rows.
The aggregate data contains the row IDs of the source row from each table in columns, and I need the ability to remove IDs from each of these.
Sources: (include head, chest, arms, legs)
CREATE TABLE head_data ( id smallint(2) unsigned NOT NULL auto_increment, ... PRIMARY KEY (id) )
CREATE TABLE combinations ( id bigint(20) unsigned NOT NULL auto_increment, head_id smallint(2) NOT NULL, chest_id smallint(2) NOT NULL, arms_id smallint(2) NOT NULL, legs_id smallint(2) NOT NULL, weight decimal(5,1) NOT NULL, height smallint(3) NOT NULL, PRIMARY KEY (id) )
What I'm doing is getting the maximum height for all items less than a certain weight and returning the head_id, chest_id, arms_id and legs_id and populating other attributes for the combination based on those.
SELECT * FROM combinations WHERE id = ( SELECT id FROM combinations WHERE height = ( SELECT max(height) FROM combinations WHERE weight < $weight ) LIMIT 1 );
This was the most efficient indexing scheme I tried (height,id and height,weight). Now I need to add functionality to this query that will allow me to remove items from "head" or "arms" from the list. My -- probably horrible -- thought is a dynamically built query depending on what the user selects such as this:
SELECT * FROM combinations WHERE id = ( SELECT id FROM combinations WHERE height = ( SELECT max(height) FROM combinations WHERE weight < $weight AND id NOT IN ( SELECT id FROM combinations WHERE head_id IN (##,##,##) OR chest_id IN (##,##,##) OR arms_id IN (##,##,##) OR legs_id IN (##,##,##) ) ) LIMIT 1 );
However, at this point, I'm not sure how to build the index for those ids. The size of an index for all possible use cases would be incredible (there are 122m rows):
head,chest,arms,legs head,arms,legs head,legs chest,arms,legs chest,legs arms,legs legs
Therefore, is there a way to set the query to have null values that would take into account the index (head,chest,arms,legs) if for example chest was not specified but legs were? For example, will "WHERE chest_id IS NOT NULL" allow the index to be used? Or do I need to make a relations table and index that in this case?
Also, if there is a more efficient way to rewrite that rather large SQL query, I'm all ears. I tried using the following with the index (height,weight,id), but it took 3x as long:
SELECT c.* FROM ( SELECT id FROM combinations WHERE weight < 20 ORDER BY height DESC limit 1 ) ids JOIN combinations AS c USING(id)