Proposal for the structure of the database
Foundation of optimized queries is the design of your database. This should ideally meet at least 3 normal forms. Closer to the standards see cs.wikipedia.org, or also useful manualy.net.
When the correct use of these normal forms, properly designed tables are correctly located. You are literally the key to the correct questions.
Types of tables in MySQL
In MySQL databases, you have to create the tables to choose from several types. Two of the most options are MyISAM and InnoDB. The basic difference between these types is the so-called. transaction-safe engine. This is a set of properties of the InnoDB table, which ensures the correct consistency of data in the individual transactions.
It offers user choice of commit and rollback, which lets the user confirm the query-commit, where appropriate, of his "odrolování"-(return the data to the State before the query)-rollback. MyISAM transaction-safe engine does not support and administration, and consistency of data to the user. However, it is slightly faster, and therefore appropriate to Internet presentations, where the programmer application shall ensure the accuracy of the data itself. More about InnoDB and transaction-safe engine, see English here.
The use of indices and the treatment with EXPLAIN
The next step to accelerate queries is to use the indexes. Indexes are data structures that allow you to quickly search marked (zaindexovaných) records. Closer to the indices can be found in a series on linuxsoft.
With the choice of the correct location of indexes can help you out of the other and the MySQL EXPLAIN command. First, by developing a query that we want to analyze, and then it simply dopíšeme EXPLAIN.
In the PHPMyAdministratoru it looks like Query Analyzer as follows:
As you can see, this command returns many useful results: each row represents the 1 table from the query (the portion FROM). For each of the possible_keys column, which expresses the design of columns for indexes. Additional columns to the key is the column listing, where are the indexes really located. Finally, the important information is the column rows. Number listed in this column represents the number of rows that MySQL has to pass through when you run a query for each table. The exact description of the command outputs the EXPLAIN in the documentation for MYSQL
Restrictions on the quantity of results-LIMIT
It is also a useful option queries limit the amount of results using the LIMIT. Using the example above. LIMIT by 1 or 2 non-negative parameters. When you use the 1 parameter defined number expresses the maximum number of lines from the beginning of the table. When you use the 2 parameters, the first number indicates the offset from the first line and the second is the maximum number of returned rows. Convenient use of this option can be found primarily in turning over the pages with the results/records.
The size of the tables
In the Administration database, we should ensure that the size of the tables-the amount of data in them. It is more than clear that the number of records is rozhodujicí for the speed of that query. In practical use, offers, for example, the distribution of data in multiple tables. For Example. We want to log the approaches people into the application. If we need to keep the logs a few years back we can for each year to create a custom table and ask questions only on a specific table.
While this may seem contrary to the normal forms, so in practice similar to, if not užitečnéjší examples use to optimize the query.
Maintenance tables
In particular for InnoDB tables, there is the possibility of calling a simple command: ALTER TABLE TableName;
This at first sight insignificant query MYSQL server internally processes so that the table lock for writing and reoptimalizuje search. Dopuručujeme to use, especially for large tables, for example. in the night (read at a time when there is no need to write to table).