MySQL Database Design and Tuning
| < Day Day Up > |
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] table cache parameter application controls setting (MySQL Administrator)table column EXPLAIN command (optimizer) output table locks explicit requests 2nd table scans avoidance of 2ndtables bad joins views, constructing 2nd bulk insert buffering speed optimization (MyISAM) 2nd column types performance considerations 2nd 3rd 4th 5th 6th 7th 8th 9th columns ENUM constraint 2nd predefined values (SET constraint) 2nd 3rd UNIQUE constraint views, constructing 2nd compressing (myisamchk utility) 2nd compression myisampack utility (MyISAM) 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 CREATE TABLE statement defragmentation (InnoDB) 2nd 3rd defragmenting (myisamchk utility) 2nd defragmenting (OPTIMZER TABLE command) 2nd 3rd DROP TABLE command 2nd foreign keys circular references designing relationships, defining usage criteria viewing 2nd indexes ascending versus descending 2nd building 2nd 3rd 4th 5th 6th 7th 8th cardinality of columns 2nd 3rd 4th 5th 6th character-based versus numeric-based 2nd 3rd compressing (MyISAM) delaying re-creation of (mysqldump utility) 2nd 3rd filter columns 2nd 3rd incorrect sorting/grouping order 2nd InnoDB, adaptive hash 2nd InnoDB, buffer pools InnoDB, data storage/structure 2nd InnoDB, foreign key generation InnoDB, memory pools InnoDB, performance boosting features 2nd 3rd 4th 5th 6th 7th join column placement 2nd 3rd logging (--log-queries-not-using indexes parameter) 2nd multicolumn type 2nd 3rd 4th 5th myisamchk utility 2nd over-indexing disadvantages partial 2nd 3rd 4th 5th 6th primary keys, defining 2nd 3rd resorting (OPTIMIZER TABLE command) 2nd 3rd SHOW INDEX command 2nd 3rd 4th 5th 6th 7th 8th sorting options (myisamchk utility) 2nd update deactivation (MyISAM) 2nd information output viewing (SHOW INDEX command) InnoDB clustered indexes 2nd secondary indexes 2nd locking granularity levels 2nd lock concurrency (InnoDB) monitoring via SHOW INNODB STATUS command (InnoDB) 2nd reloading data from data export operations 2nd row-level locking scope (InnoDB) 2nd lookup design mistakes 2nd optimizing via ANALYZE TABLE command 2nd via EXPLAIN command 2nd 3rd 4th 5th 6th 7th 8th 9th 10th 11th 12th 13th 14th 15th 16th 17th 18th 19th 20th 21st 22nd 23rd 24th 25th 26th 27th 28th 29th 30th 31st 32nd via OPTIMIZER TABLE command 2nd 3rd parallel index creation (myisamchk utility) queries bypassing certain operations 2nd caching 2nd HANDLER statement 2nd 3rd 4th in-memory temporary type, forcing sorting operations 2nd 3rd 4th temporary creation of 2nd UNION operator 2nd 3rd 4th 5th 6th 7th READ locks 2nd row format specifying 2nd 3rd rows views, constructing 2nd schemas copying without associated data 2nd size key write delays speed optimization (MyISAM) 2nd size specifications speed optimization (MyISAM) 2nd sizes specifying sorts optimazation factors optimization factors 2nd statistics deleted blocks section (myisamchk utility) fragmentation section (myisamchk utility) index compression section (myisamchk utility) index depth section (myisamchk utility) retrieving (ANALYZE TABLE command) status section (myisamchk utility) storage engines ARCHIVE selection Berkeley Database (BDB) selection CSV selection 2nd FEDERATED selection 2nd InnoDB selection 2nd MaxDB selection MEMORY selection 2nd 3rd MERGE selection 2nd 3rd 4th MyISAM selection 2nd 3rd 4th 5th 6th 7th 8th 9th NDB CLUSTER selection selection options 2nd storing in column-sorted order ALTER TABLE, ORDER BY statement 2nd storing in index order (myisamchk utility) 2nd structure optimization methods 2nd 3rd 4th 5th temporary storage of (High-Hat Airways scenario) 2nd temporary types query improvements 2nd 3rd TRUNCATE TABLE command 2nd unlocking views query performance 2nd updating with integrity checking 2nd WRITE lockstablespaces (InnoDB) data files 2ndTCP/IP clustering issues (MySQL Cluster) 2nd connectivity tuning 2nd TCP/IP local transporters (nodes) TCP/IP remote transporters (nodes) temporary directories disk drives distributing 2nd temporary files slave servers storage of temporary tables queries creating 2nd performance tips 2nd 3rd storage of (High-Hay Airways scenario) 2nd terminating threadstest plans performance analysis pretesting preparations 2nd 3rdTEXT column indexes defining 2nd text-based data exports executing 2nd 3rd thread cache size variable thread cached size variable thread stack variable threads InnoDB innodb thread concurrency setting 2nd memory settings database connectivity 2nd termination of timeout parameters replication servers performance issues 2ndtimeouts IIS/PHP performance configuration connection settings tmpdir service variable 2ndtools performance monitoring binary log 2nd 3rd 4th free (Linux) general query log 2nd IBM Performance Monitoring (rmfpms) 2nd iostat (Linux) MMC Performance Monitor 2nd mpstat (Linux) MySQL Administrator 2nd MySQL Query Browser 2nd mysqladmin 2nd netstat (Linux) 2nd ps (Linux) Red Hat Linux System Monitor sar (Linux) 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 slow query log 2nd top (Linux) vmstat (Linux) Windows Task Manager 2nd top utility (Linux) topologies MySQL Cluster selection of 2nd 3rd server outage solutions High-Hat Airways scenario 2nd web servers fast interserver communciation 2nd multiserver configurations 2nd selecting 2nd 3rd 4th 5th 6th tracking package status (High-Hat Airways) query problems 2nd diagnosing 2nd 3rd resolving 2nd 3rd transaction alloc block size variabletransaction bottlenecks High-Hat Airways case study diagnosing 2nd 3rd order of solution implementation 2nd replication strategy resolving 2nd resource restriction strategy rollup strategy transaction prealloc size variabletransactions costs 2nd High-Hat Airways failure diagnosis 2nd 3rd 4th failure solution 2nd failure solution;timeout settings 2nd 3rd 4th performance tips brevity of 2nd 3rd 4th 5th 6th isolation level selection 2nd 3rd 4th storage engine selection user interface communication transporters (nodes) 2nd Scalable Coherent Interface (SCI) shared memory TCP/IP local TCP/IP remotetriggers function of limitations 2nd when not to use 2nd when to use 2nd TRUNCATE TABLE command 2ndtuning InnoDB data storage 2nd 3rd 4th log files InnoDB 2nd 3rd 4th tuning tips Apache/PHP web servers 2nd bottleneck elimination 2nd build suggestions HostNameLookups setting 2nd KeepAlive setting KeepAliveTimeout setting MaxClients setting MaxKeepAliveRequests setting MaxRequestsPerChild setting version upgrades connectivity creation costs 2nd 3rd destruction costs 2nd 3rd hostname resolution costs 2nd named pipes 2nd Scalable Coherent Interface (SCI) shared memory sockets TCP/IP selection 2nd IIS/PHP web servers bandwidth limits 2nd CGI improvements 2nd connection timeouts HTTP compression Linux/Unix 2nd disabling unnecessary services 2nd recent kernel upgrades 2nd PHP web server connections 2nd caching options 2nd initialization file configuration 2nd Windows disabling extraneous services 2nd execution of MySQL server process 2nd server OS selection spyware prevention 2nd type column EXPLAIN command (optimizer) output 2nd 3rd 4th 5th |
| < Day Day Up > |