PHP – In this section, we discuss various tools and techniques for using SQL and MySQL. Introduction The choice of keys, and indexes for fast searching techniques basic database development add and remove users from a DBMS, and user permissions evolution Limitations of MySQL discussed a third 10th key, primary keys and indexes in our earlier introduction of SQL, so that each table has a primary key defined in the CREATE TABLE statement. A primary key is an attribute or attributes that uniquely identifies a row in a table. Store two rows with the same primary key is not allowed, yes, an attempt to insert duplicate primary key generates an error. In MySQL, the attribute values of the primary key will be stored in an index for quick access to a line. The index type standard MySQL is fast querying to find a particular line, a range of lines for the relationship between tables, group data, order data, and find the minimum and maximum values. Indices give no improvement in speed to retrieve all the rows in a table or other types of applications. The indices are also useful for quick access to lines of values other than those associated with the primary key attributes. For example, in the customer table, you can create an index by the clause: city Key Name (full name, city) to define the CREATE TABLE statement. Having defined the index, some queries that select a particular client by a WHERE clause, you can use. Consider an example: SELECT * FROM customers WHERE name = ‘Marzalla “and firstname =’ Dimitria and City = ‘St. Albans; This request may find the new index in more than a few hard knocks, the line that matches the search criteria. Without the index, the DBMS-scan all rows in the customer table and compare each line with the WHERE clause. This could be very slow and certainly requires much more disk access than the approach based on an index (assuming that the table has more than a few lines). A peculiarity of database systems is that they develop a strategy for query evaluation and optimization, without any interaction from the user or programmer. If an index exists, and it is logical to use in a query, the DBMS will do it automatically. Everything you need to do is know what the complaints are common, and create an index for the top queries for the key clause of the CREATE TABLE or ALTER TABLE to an existing table. Index careful design is important. The index name of the city can speed up queries, we defined other than a full name, first name of the coverage, and the city. For example, consider a query: SELECT * FROM customers WHERE name = ‘name and first LaTrobe =’ Anthony ‘; This query can name the city with the index because the index provides access to the records ordered by first order name, then, and then the city. With this command, all the “Latrobe, Anthony will be” clustered index entries in the index. In fact, the index can also be used for the query: SELECT * FROM customers WHERE name LIKE ‘% in March, again, all names starting with “March” are grouped in the index. However, the index can not be used for a query like: SELECT * FROM Customers Where name first = ‘Dimitria and City =’ St. Albans; The index can not be used as the attribute to the left index, names are called, is not a part of the WHERE clause. In this case, all rows of the table and scanned the customer query is much slower (again, assuming there is more than a few lines in the customer table, and if no other index ). Careful selection of the order of attributes in a key clause is important. For an index to be adapted in a query, then left the attribute appear in a WHERE clause. There are other cases in which an index is not used, for example, if a query contains one or are not indexed to an attribute: SELECT * FROM customers WHERE name = ‘Marzalla “or email =’ @ can be lucaston Dimitria. com, again, the customer table will be scanned completely, because the second condition, email = ‘@ lucaston Dimitria. Com “, requires that all lines are extracted, because there is no index on the attribute e-mail. In addition, if, if it is not the attribute of the attribute or to the left to browse an index of a table full of customers. The following example requires a complete analysis: SELECT * FROM Customers Where name first = ‘OR Name = Dimitria’ Marzalla “if all the attributes of the index used in all applications, optimizing the size of indexes, the attribute in the left clause was essential attribute to be the largest number of duplicates. Since indexes speed up queries, why not all the indexes on the attributes that you may be looking for less? The answer is that the updates while the indexes are fast for users, they consume space and require every time the lines added or deleted, or key attributes are changed. So, if a database is largely static, low overheads have additional clues, but if a change of database frequently, each additional indication of the update process will be slowed considerably. In both cases, the indexes consume additional space and unnecessary indices should be avoided. One way to reduce the size of an index of updates and speed is an index on a prefix of an attribute to create. Our index of the city name used considerable space: the client for each row of the table, an index entry will be up to 120 characters due to the combined values of the name, given name is created, and attributes the city. [2] In space, you can specify how the index: [2] This is not the actual space required by an index entry, as the data is compressed for storage. But even with compression, the indices of characters unless the representation more compact, space saving, and the ease of using the index search, the fastest and updates. key city name (name (10), first (3) City (2)), which uses only the first 10 characters of your name and 3 of the first two characters of the city to distinguish index entries . This makes sense as 10 different characters of the same family between most family names, and adding a few characters from a given name and prefix of their town should be sufficient to identify virtually all customers. With a smaller index, less information may mean that the complaints are actually faster because more information about the index can be retrieved from disk per second, and accelerate the recovery drive is almost always bottleneck in the performance of queries. Space savings is a significant reduction index. A new index entry requires only 15 characters, a savings of up to 105 characters, so that the index insertions, deletions and changes may be much faster now. Note that text and BLOB attributes, a prefix must be taken if the index because the index is the integer attribute is impractical and not accepted by the MySQL database. 3rd 10th 2 Search the database indexing system of careful design is a technique that improves the speed of a DBMS and can reduce the resource requirements of a database. However, a comprehensive database of adjusting a complex subject that fills many books. We are in this section some other practical ideas to start improving the performance of a database. As already discussed access to a hard disk is slow and is usually the bottleneck in the performance of DBMS. Specifically, the disk seeks to move the disk head to get information from another part of the plate is the slowest component of disk access. Therefore, most techniques described in this section, techniques to improve performance by minimizing the disk space requirements. [3] [3] The reduction in disk space enhances both the hard drive search and playback performance. Disk read performance is improved because less data must be transmitted during the execution of search is improved because the disk head must move less, on average, if random access to a smaller file that access to a larger file. Here are some ways to improve the performance of DBMS: Choose carefully attribute types and lengths. Where possible, use versions as small as SMALLINT or MEDIUMINT instead of INT regular election. When using fixed-length attributes, such as char, specify a length as short as possible. With the attributes of fixed length, which is trying to avoid guys like VARCHAR or BLOB. While the fixed-length text attributes wasting space, sweeping lines of fixed length on a motion is much faster than scanlines of varying lengths. Design clues carefully. As discussed in the previous section, contain the primary key index as small as possible, simply create indexes that are needed, and the use of prefixes attributes where possible. Make sure the attribute is left in the index most frequently used in queries, and if all attributes are used, make sure the left is the attribute with the largest number of duplicates. Create a table with the statistics, if aggregate functions like COUNT () or SUM () are frequently used in queries on large tables. A statistical table stores a single line that is updated manually with the aggregate values from another table. For example, if the table statistics on the number of rows in a table of large customers, whenever a row is inserted or deleted in the customer table, the account is updated in the statistics table. For large tables, which is often faster than the computation of aggregate functions with the slowness of integrated functions, the complete processing of all necessary lines. If a large number of rows in a table or a table with variable length attributes is deleted frequently amended, may be wasted space. MySQL usually not deleted or removed data has changed, but only the location marked as no longer in use. Wasted Space can affect the speed. To reorganize a table by copying the data into a temporary directory, then re-MySQL has the OPTIMIZE TABLE should be used regularly. For example, OPTIMIZE TABLE customer; The OPTIMIZE command to execute when the DBMS is offline for maintenance. The command is not standard SQL. It is possible to create different types of tables for specific tasks. The default MySQL MyISAM is the path and all the tables previously described in this table. be used for small, temporary, often used lookup tables, a different way, the table type heap. There are other species, and alternatives, are discussed briefly in Chapter 6 Further details are provided in § 9. 4 of the MySQL manual. § 10 7 MySQL manual for more good ideas for easy implementation. Another aspect of database tuning optimizes the performance of the DBMS itself. Included with the installation of MySQL, the mysqladmin tool for administering database. Information on the system configuration can be found by running the following command from a Linux shell: mysqladmin variables% ppassword This shows some of the following parameters of the chosen system: the current value join_buffer: 131,072 key_buffer current value: 8388600 current net_buffer_length Value: 16384 record_buffer current value: 131072 sort_buffer current value: 2097144 table_cache current value: 64 The most important parameters are the ones to benefit from the hard impact. MySQL has several parameters of the main memory buffer, the amount of data in memory is retained for the control of processing. These include: the record_buffer for scanning through all rows in a table for ORDER BY and GROUP BY sort_buffer key_buffer index in main memory for storing join_buffer for joins not using indexes, generally, the more these buffers more data is cached on the hard disk or in memory and disk access are less necessary. However, if the sum of these parameters is close to exceeding the amount of memory installed on the server, the operating system underlying will start to exchange data between disk and memory, and the DBMS will be slow. In each case, careful experiments on the basis of the request is likely to improve the performance of DBMS. 10 § 3 of the second MySQL manual recommends settings when starting the MySQL server. First, for machines with at least 64 MB of memory, large tables in the Works database, and a moderate number of users, use: key_buffer safe_mysqld-O = 16M table_cache sort_buffer record_buffer-128 OO = 4m & = 1M Second, if it is less than 64 MB of available memory, and there are multiple users, do the following: key_buffer safe_mysqld-O = 512k-100k sort_buffer O = O = 100k and could record_buffer The following setting for the wine merchant’s appropriate, since many users are expected, applications are mostly based on an index, and the database is low key_buffer safe_mysqld-O-O = 512k = 16k sort_buffer table_cache record_buffer-OO = 32 = O = 1K-8K & net_buffer more conservative parameters can also be accepted. There are two other parameters that we have not discussed. The parameter table_cache controls the maximum number of open tables per user port, while the parameters net_buffer the minimum size of network applications should be kept in buffer incoming requests before they are executed. The mysqladmin utility can report the status of the DBMS:% mysqladmin status ppassword The output has the following format: Uptime: 5721024 Threads: 14 Questions: 7874982 Slow queries: 6: 115 136 Open FLUSH TABLES: 1 Open tables: 62 The result a brief point in time the state of DBMS and can help you find and use information on the number of user connections, queries and tables. similar output variables can be generated by the shell running MySQL SHOW STATUS and SHOW. Information about query performance, with the benchmark () function, which are used iteratively adjusting, changing table design, or if the DBMS system parameters are obtained. The following statement illustrates Benchmarking: SELECT Reference (10000, COUNT (*)) items; This statement returns the time required for 10,000 calls to COUNT () the array elements to evaluate. 10th 3rd 3 Adding and deleting users, we have not discussed adding and deleting users from MySQL database. Our basic principle when we leave this subject until the last section that DBMS users are not as important in an application of web database and other applications. Since access to the database and DBMS in general in the application logic tier intermediary is needed usually only one or two DBMS user controlled. One user, Hugh, who has full control over all aspects of the DBMS and the DBMS can from the machine hosting the DBMS access can be created with the following statement: grant privileges ALL ON *. * Hugh @ localhost IDENTIFIED by password ‘WITH GRANT OPTION access on a network may be included: grant privileges ALL ON *. * Hugh @ “%” identified by password ‘WITH GRANT OPTION; It is not necessary to enable network access for an application of web database, the middle-tier components and Web server scripting engine for computer are installed, such as the DBMS. The user can then connect to the database from the shell with the command: mysql-uhugh ppassword% user information in the MySQL database in the user table that can be explored with: using MySQL, SELECT * FROM Users is stored, the MySQL database and the user table managed in the same manner as any other database. For example, you can update the password for the new user with the UPDATE statement: UPDATE SET user password = PASSWORD (‘your’) WHERE user = ‘Hugh; Note the use of the function PASSWORD (), we have already described password encrypt the user for storage in the table. 3rd 3rd 10th permissions a user can manage the system with an INSERT INTO table added user in the mysql database, or, as mentioned above, you can use the GRANT command. In addition, privileges can be set to update, taken with or removed with REVOKE GRANT. Consider the following example: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON wine store. * Dave @ localhost IDENTIFIED by password “, which adds a new user, Dave and allows it only listed the SQL database in the wine store. The wine merchant parameter. * Means that all the tables in the database winestore. Privileges can be removed with REVOKE. For example: REVOKE DROP, CREATE on the wine store. * Dave @ localhost; If the prerogative and privilege for all databases in the DBMS, not just a single database, wine merchant, is revoked. * Can be used with *. replaced *. The following permissions can GRANT and REVOKE are used: all the privileges, FILE, Reload, ALTER INDEX SELECT CREATE INSERT SHUTDOWN DELETE PROCESS, UPDATE, REFERENCES, DROP, 10th third use 4 Limitations of MySQL is the restriction of more important than MySQL does not support nested queries. However, support is provided in the MySQL 4. Nested queries are those that contain another query. Consider an example of nested query to find the wines that stock: SELECT DISTINCT FROM wine_id wine, where IN (SELECT wine_id wine_id inventory) The motion wine_id values of table wine in the inventory table to be found . Nested queries using the IN, NOT IN, EXISTS and NOT EXISTS operators. In many cases, a nested query as a join query to be rewritten. For example, the wines are in the camp, you can use the join query: SELECT DISTINCT wine. wine_id wine, broth, where the wine. = Inventory wine_id. wine_id; However, some nested queries can not be rewritten as a join query, for difficult queries, temporary tables are often a useful workaround. A limitation of the DELETE and UPDATE is only one table in the FROM clause can be specified. This problem is particularly important for MySQL and related to the lack of support for nested queries. This limitation, changes in the data difficult. For example, prevents the data are deleted or updated with the properties of the other table. One solution is to file in a temporary table with an INSERT SELECT statement and combined, which combines data from several tables are copied. Subsequently, the data can be deleted or updated in the temporary table, then transferred to the original table. Another approach, with the concat () string function is discussed in Section 1. A fourth fourth in the MySQL manual. To avoid, UPDATE, DELETE and problems, consider additional attributes on the tables at design time. For example, in the wine shop we have a date attribute to the table so that objects can basket items are removed easily, if not bought in a day. Removing rows from the Products table based on the date of the Orders table is difficult without the support of nested queries. MySQL does not support stored procedures or triggers. Stored procedures are queries that are collected and stored in the DBMS. You will then be called from the average level of application logic, with the advantage that the application is read only once, and there is less overall communication between levels central database. Triggers are similar to stored procedures, but are called by the DBMS if a condition is met. MySQL stored procedure support provided, but the support is not traveling. Views are not supported by MySQL. Views consolidate access read-only multiple tables on a join condition based. For example, a glance, a user can browse through April sales without the need to create a temporary table, as we have the example in section 3. 8th See the support is planned for the future. Limitations we are not here to discuss the lack of support for foreign keys and cursor. Further details on the limitations of MySQL can be found be found in Section 1. 4 of the manual supplied with MySQL. More PHP Tutorial