MySQL

Database performance is critical to a data-centric application that directly affects the performance of the program. In general, to ensure the efficiency of the database, to do the following four aspects of the work: database design, SQL statement

http://zhangxugg-163-com.iteye.com/blog/1666673The federated described in this article belongs to MySQL's special engine, which can be used to map local data tables to a remote MySQL data table to solve a wide range of cross-machine connection

1. About multi-table queriesI understand: Since MySQL multi-table query when the connection between the table is a Cartesian product, so as little as possible to use a multi-table query, if used to use nested statementsExample: Description: '

1. To optimize the query, avoid full-table scanning as far as possible, and first consider establishing an index on the columns involved in the Where and order by.2. Avoid null-valued fields in the WHERE clause, which will cause the engine to

PHP connection MySQL is the first step in the database operation, in PHP, you can use the built-in PHP function mysql_connect () to implement, the function format is mysql_connect ("MySQL hostname", "MySQL User name", "MySQL password" ), the

1. Obtained by the self-increment key auto_increment.Select Max (ID) from TableNameThis approach requires consideration of concurrency and the need to "X-lock" the primary table in a transaction, and then unlock it after the value of Max (ID) is

Add an account: Code: (Requires that you have already logged in to MySQL using root)Grant on the database. * to User name @ Login Host by password;Attention:1. The database must be followed by a. *,2. Permissions are divided into select

MySQL replication is a multi-MySQL database master from a synchronous scheme, characterized by asynchrony, widely used in a variety of situations where MySQL has higher performance and higher reliability requirements. Another technique that

Integral type data: 1.tinyint,2.smallint,3.mediumint,4.int,5.bigint.InvasiveWhen the statement was made, we found that there was a statement:Create Table main_t ( tinyint ),smallint(), meidum_id mediumint ( ),int(a), bigint());

Modify Table Name: Code:Alter table Old table name rename new table name;Example:Alter table peo rename people;Note: After completion can be used to show tables check table name is modified;To modify the data type of a table field: Code:

Workaround:1. Management tool---Service inside stop MySQL service.2. Control Panel---Uninstall MySQL, delete C:\Program files\mysql directory.3.This is the most critical step, only the first two steps, the password or can not be modified, because

In database tables, using indexes can greatly improve query speed. Suppose we create a testindex table:CREATE TABLE Testindex (i_testid INT not null,vc_name VARCHAR () notnull);We randomly inserted 1000 records into it, including a i_testid vc_name 5

MyISAM is the default storage engine for MySQL, based on the traditional ISAM type, which supports full-text search, but is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in three files: the frm file is stored as

MySQL master-slave replication test environment:The 1.centos5.8 x86_64 is minimized, and the system has undergone a basic optimization2.mysql version: 5.1.73-log, multiple instances of the same server, installation method reference: MySQL

About the MySQL character set problem "resolved"Let's take a look at my character set settingsIt's all UTF8. There's nothing wrong here.Next I created a data table book. Look at the table structure I createdWhen input command:mysql> insert into book

The first is to create the viewCREATE algorithm=undefined definer= ' root ' @ ' localhost ' SQL SECURITY definerVIEW ' New_view 'As (select ' Girls ') ' id ' as ' id ', ' Girls '. ' Name ' as ' name ', ' Girls '. ' Age ' as ' age ', ' girls ', '

command: show Processlist;if it is the root account, you can see the current connection for all users. If you are a normal account, you can only see the connection you are occupying. show Processlist; only the first 100 are listed, if you want to

Optimization steps:1. See if SQL can be optimized.2. See if the index can be optimized.3. See if the table structure can be optimized.Show table status from databases like ' tablename% '; View the capacity, index, and size of a data file for a

MySQL execution processWe can manually divide the main functions of MySQL into the following modules.1. Initialize the moduleWhen MySQL starts to perform initialization work, such as reading the configuration file, assigning some global variables

MySQL: Detailed description of MySQL date data type and MySQL time typeI. MySQL date type: Date Format, storage space occupied, and date range comparison Date Type Bucket Date Format date range ------------ -------------------------------------------