Examples of Debugging a Slow Query

In this section, we use a table called PLAYERS. This table is created when the NuoDB SQL Quickstart is run (see Running the SQL QuickStart). It contains a FIRSTNAME and LASTNAME column. There are more than 7,000 rows in this table.

For the purpose of this discussion, a couple of changes need to be made to illustrate the behavior of the NuoDB optimizer.

Update several rows so that an inordinate number of rows in the PLAYERS table will have the firstname= “Mike”.

An index needs to be added to the PLAYERS table on the column FIRSTNAME.

UPDATE players SET firstname = 'Mike' WHERE firstname <> 'Kurt' LIMIT 4000;

CREATE INDEX idx_players_firstname ON players (firstname);

SHOW TABLE players;
    Tables named PLAYERS
    Found table PLAYERS in schema HOCKEY
        Fields:
            PLAYERID varchar(10)
                Nullable: No
            FIRSTNAME varchar(32)
            LASTNAME varchar(32)
            HEIGHT integer
            WEIGHT integer
            FIRSTNHL integer
                Nullable: No
                Default: 0
            LASTNHL integer
                Nullable: No
                Default: 0
            POSITION varchar(8)
            BIRTHYEAR integer
            BIRTHMON integer
            BIRTHDAY integer
            BIRTHCOUNTRY varchar(32)
            BIRTHSTATE varchar(32)
            BIRTHCITY varchar(32)
        Primary Index: PLAYERS..PRIMARY_KEY on field: PLAYERID
        Secondary Index: IDX_PLAYERS_FIRSTNAME on field: FIRSTNAME