MySQL Database Design and Tuning

Index

[SYMBOL] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] caches    MyISAM engine

         leveraging for optimal searching 2ndcaching     binary logs 2nd 3rd     PHP web server connections 2nd

     queries 2nd

calculating    math queries         performance guidelines 2nd 3rd 4th 5th 6th 7th

cardinality column

     SHOW INDEX command outputcase study (High-Hat Airways)

     package delivery scenario 2nd 3rd         package status lookup problems 2nd         package status lookup problems, diagnosis 2nd 3rd

         package status lookup problems, solution 2nd 3rd

         shipping option lookup problems 2nd

         shipping option lookup problems, diagnosis 2nd

         shipping option lookup problems, resolving 2nd 3rd    random transaction bottlenecks

         diagnosis of 2nd 3rd

         order of solution implementation 2nd         replication strategy

         resolution of 2nd         resource restriction strategy         rollup strategy

CGI

     IIS/PHP performance configuration 2nd

CHAR BINARY text type

    versus VARCHAR BINARY text type

         column performance implications

CHAR text type

    versus VARCHAR text type

         column performance implications

character-based indexes

     versus numeric-based indexes 2nd 3rd

character-based tools (performance monitoring)

     mysqladmin 2nd

     SET/SELECT command 2nd

     SHOW ENGINES command 2nd

     SHOW INDEX command 2nd

     SHOW INNODB STATUS command 2nd 3rd     SHOW PROCESSLIST command

     SHOW STATUS command 2nd 3rd

     SHOW TABLES command     SHOW TABLES STATUS command 2nd 3rd     SHOW VARIABLES command 2nd checkpoints     global

     local     node storagechecksum     integrity management (MyISAM engine) 2nd

CHECKSUM TABLE command 2nd

clients     clusterclustered indexes

    InnoDB

         rowid values 2ndclustering

     advantages 2nd 3rd     benefits 2nd     clients

    data nodes

         MySQL Cluster 2nd

         MySQL Cluster, capacity parameters 2nd

         MySQL Cluster, logging parameters 2nd         MySQL Cluster, memory parameters 2nd 3rd

         MySQL Cluster, node timing parameters 2nd 3rd

         MySQL Cluster, transaction parameters 2nd 3rd    management nodes

         MySQL Cluster, arbitration issues 2nd         MySQL Cluster, loggin issues         MySQL Cluster, logging issues

    network issues

         MySQL Cluster, dedicated subnets

         MySQL Cluster, numeric IP addresses

    network protocols

         MySQL Cluster

         MySQL Cluster, SCI socket issues 2nd

         MySQL Cluster, shared memory issues 2nd

         MySQL Cluster, TCP/IP issues 2nd

    nodes

         checkpoints 2nd 3rd 4th

         data 2nd

         data distribution 2nd

         function of

         management

         SQL Node 2nd

         transporters 2nd 3rd 4th 5th 6th     optimization overview 2nd 3rd

    performance tips

         MySQL Cluster 2nd         MySQL Cluster, data protection 2nd         MySQL Cluster, grouping insert operations         MySQL Cluster, load balancing 2nd         MySQL Cluster, primary key employment

         MySQL Cluster, proper indexes    server problems         diagnosing (High-Hat Airways scenario) 2nd 3rd 4th         resolving (High-Hat Airways scenario) 2nd 3rd 4th 5th

     shared-nothing nodes

    SQL nodes         MySQL Cluster, arbitration issues         MySQL Cluster, batching issues 2nd

    strategies

         MySQL Cluster topology selection 2nd 3rd         MySQL Cluster version selection 2nd

     versus replication 2nd 3rdcoding     efficient application writing practices 2nd

         multirow INSERT statements 2nd

         ODBC configuration 2nd

         persistent connections 2nd

         prepared statements 2ndcollation column

     SHOW INDEX command output

column_name column     SHOW INDEX command output

columns    BLOBs         performance implications 2nd

    CHAR BINARY text type versus VARCHAR BINARY text type

         performance implications

    CHAR text type versus VARCHAR text type

         performance implications

    lookup tables

         design mistakes 2nd

    numeric types

         performance implications 2nd

    similar values

         partial index use 2nd

    tables

         data handling (HANDLER statement) 2nd 3rd 4th

         ENUM constraint 2nd

         index cardinality 2nd 3rd 4th 5th 6th

         performance considerations 2nd 3rd 4th 5th 6th 7th 8th 9th

         predefined values (SET constraint) 2nd 3rd         UNIQUE constraint

    TEXT/BLOB values

         partial index use 2ndcommands     CHECKSUM TABLE 2nd     SHOW BINARY LOGS     SHOW BINLOG EVENTS 2nd

     SHOW INDEX 2nd         cardinality column         collation column         column_name column

         comment column

         index_type column         key_name column         non-unique column

         non_unique column

         null column         packed column

         results output 2nd 3rd 4th         results output, interpretation of 2nd         sub_part column

     SHOW INNODB STATUS

     SHOW MASTER LOGS

     SHOW MASTER STATUS

     SHOW SLAVE STATUS 2ndcomment column

     SHOW INDEX command output

compressing    indexes

         MyISAM    tables         myisamchk utility 2nd

     tables (MyISAM)

compression algorithm

     replication server traffic

concurrency

    data exports

         --lock-tables attribute 2nd

         --single-transaction attribute 2nd

    data reloading

         LOAD DATA INFILE statement with CONCURRENT keyword

         LOAD DATA INFILE statement with LOW PRIORITY keyword

         potential problems

    InnoDB

         performance tips

     insert delayed statements 2nd

     MyISAM speed optimization

         concurrent insert variable         query cache locking

     unlocking tables

concurrent insert variable Configuration Wizard     sample configuration example 2nd 3rd 4th    servers         customized versus general-purpose configuration 2nd

         InnoDB tablespace configuration         number of connections         processing profiles         protocol selection

         security options 2nd

configuring    disk drives         memory caches 2nd

    InnoDB

         data storage 2nd 3rd 4th     key cache

         10% memory allocation rules 2nd         memory allocations         midpoint insertion strategy 2nd

         size options

    log files

         InnoDB 2nd 3rd 4th

     query caches 2nd 3rd    slave servers

         resource management

connect timeout setting     database connectivity

connectivity    performance analysis         pretesting preparations 2nd

    tuning tips

         creation costs 2nd 3rd

         destruction costs 2nd 3rd

         hostname resolution costs 2nd

         named pipes 2nd

         Scalable Coherent Interface (SCI)

         shared memory

         sockets

         SSL costs 2nd

         TCP/IP selection 2nd

connectivity (databases)

     creating 2nd 3rd 4th

     managing 2nd 3rd

     optimization factors 2nd 3rd 4th 5th 6th 7th

         aborted clients status setting

         application control 2nd         back log setting

         binary log caching 2nd 3rd

         concurrency 2nd 3rd         connect timeout setting         flush commands setting         in-memory caches 2nd 3rd 4th 5th 6th 7th 8th 9th 10th 11th 12th 13th 14th         interactive timeout setting

         joins performance         locking mysqld in memory 2nd         max allowed packet setting         max connections setting

         max used connections setting

         MEMORY tables 2nd         net buffer length setting         net read timeout setting

         net retry timeout setting

         net write timeout setting         optimizer control 2nd 3rd 4th

         sort performance 2nd 3rd         table scans 2nd         thread memory settings 2nd

         user resource controls 2nd

    session dropping factors

         inactivity 2nd 3rd

         interruptionsconnectors

     (product suite category)

     function of     ODBC

constraints     benefits 2nd     DEFAULT 2nd 3rd

     ENUM 2nd 3rd

     FOREIGN KEY 2nd 3rd 4th 5th 6th

     function of

     NOT NULL 2nd 3rd

     PRIMARY KEY 2nd 3rd

     SET 2nd 3rd 4th

     UNIQUE 2nd

     violations of

CONTINUE error handler 2nd

CREATE TABLE statement

CSV storage engine

     selection criteria 2nd