0

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)
)

Combinations Table

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.

The Query:

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)
  • Whenever you use LIMIT, have an ORDER BY. – Rick James May 18 '15 at 5:13
  • WHERE weight < 20 begs for INDEX(weight). – Rick James May 18 '15 at 5:14
  • NOT IN ( SELECT ... ) is very inefficient. Change to LEFT JOIN ... WHERE ... IS NULL. – Rick James May 18 '15 at 5:15
  • I read that NOT IN ( SELECT ... ) is actually more efficient than LEFT JOIN etc in this case since there are many rows being filtered out. Each id excluded would result in about 1,000,000 rows being filtered out explainextended.com/2009/09/15/… – johnrom May 19 '15 at 4:52
  • I would be interested to read that reference. – Rick James May 19 '15 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.