Mysql examples of complex queries with parameters. Indexing and using the same types for linked columns
Currently, everyone can observe the rapid growth in the volume of digital information. And since most of this information is important, there is a need to store it on digital media for later use. In this situation, modern technologies such as databases can be used. They provide secure storage of any digital information, and data can be accessed anywhere globe. One of the technologies under consideration is the MySQL database management system.
MySQL DBMS - what is it?
MySQL is one of the most popular and frequently used information storage technologies. Her functionality superior in many respects to existing DBMSs. In particular, one of the main features is the ability to use MySQL nested queries.
Therefore, many projects where performance time is important and it is necessary to ensure information storage, as well as carry out complex data retrievals, are developed based on MySQL DBMS. Most of these developments are Internet sites. At the same time, MySQL is actively being implemented in the implementation of both small (blogs, business card websites, etc.) and fairly large tasks (online stores, etc.). In both cases, a MySQL query is used to display information on a website page. In the query, developers try to make maximum use of the available capabilities provided by the database management system.
How should data storage be organized?
For convenient storage and subsequent processing, the data must be organized. The data structure allows you to determine how the tables used to store information will look. Database tables are a set of fields (columns) responsible for each specific property of a data object.
For example, if a table of employees of a certain company is compiled, then its most simple structure will look like this. Each employee is assigned unique number, which is usually used as primary key to the table. Then the employee’s personal data is entered into the table. It can be anything: full name, number of the department to which it is assigned, telephone number, address, etc. According to the requirements of normalization (6 normal forms of databases), as well as in order for MySQL queries to be structured, table fields must be atomic, that is, not have enumerations or lists. Therefore, as a rule, there are separate fields in the table for last name, first name, etc.
Ivanovich | Admin. | Director | |||||
Petrovich | Admin. | Deputy directors | |||||
Gregory | Grigorievich | Boss | |||||
Sergeevich | Sales consultant. |
Above is a trivial example of a database table structure. However, it does not yet fully meet the basic requirements of normalization. IN real systems An additional department table is created. Therefore, the table above should contain department numbers instead of words in the “Department” column.
How data is sampled
To obtain data from tables in a DBMS, use special team MySQL- request Select. In order for the server to respond correctly to the request, the request must be formed correctly. The request structure is formed as follows. Any call to the database server begins with a keyword select. It is from this that all queries in MySQL are built. The examples may vary in complexity, but the construction principle is very similar.
Then you need to indicate from which fields you want to select the information of interest. The fields are listed separated by commas after the sentence select. After all the necessary fields have been listed, the query specifies the table object from which the selection will occur using the clause from and specifying the table name.
To limit the selection, special operators provided by the DBMS are added to MySQL queries. To select non-repeating (unique) data, use the clause distinct, and to set conditions - the operator where. As an example applicable to the above table, consider a query requesting full name information. employees working in the Sales department. The request structure will look like in the table below.
The concept of a subquery
But the main feature of the DBMS, as mentioned above, is the ability to process nested MySQL queries. What should it look like? From the name it is logically clear that, formed in a certain hierarchy of two or more queries. In theory for studying the features of a DBMS, it is said that MySQL does not impose restrictions on the number of MySQL queries that can be nested in the main query. However, you can experiment in practice and make sure that after the second dozen nested requests, the response time will significantly increase. In any case, in practice there are no tasks that require using an extremely complex MySQL query. A query may require a maximum of 3-5 nested hierarchies.
Building Nested Queries
When analyzing the information read, a number of questions arise about where nested queries can be used and whether it is possible to solve the problem by breaking them down into simple ones without complicating the structure. In practice, nested queries are used to solve complex problems. This type of problem includes situations where the condition by which further sampling of values will be limited is unknown in advance. It is impossible to solve such problems if you simply use a regular MySQL query. A query consisting of hierarchies will look for constraints that may change over time or may not be known in advance.
If we consider the table above, then a difficult task can be given next example. Let's say we need to find out basic information about the employees subordinate to Grigory Grigorievich Grishin, who is When we formulate the request, we do not know his identification number. Therefore, initially we need to know it. To do this, use a simple query that will allow you to find a solution to the main condition and complement the main MySQL query. The query clearly shows that the subquery receives an employee identification number, which further defines the restriction of the main query:
In this case, the proposal any is used to eliminate errors if there are several employees with such initials.
Results
To summarize, it should be noted that there are many more additional features, which greatly facilitate the construction of queries, since the MySQL DBMS is a powerful tool with a rich arsenal of tools for storing and processing data.
The content of the article
1.
The simplest MySQL queries
2.
Simple SELECT queries
3.
Simple INSERT (new entry) queries
4.
Simple UPDATE(rewrite, append) queries
5.
Simple DELETE (delete record) queries
6.
Simple DROP (delete table) queries
7.
Complex MySQL queries
8.
MySQL queries and PHP variables
1. The simplest SQL queries
1. Displays a list of ALL databases.SHOW databases;
2. Lists ALL tables in the base_name Database.
SHOW tables in base_name;
2. Simple SELECT queries to the MySQL database
SELECT– a query that selects existing data from the database. You can specify specific selection parameters for selection. For example, the essence of the request in Russian sounds like this: SELECT such and such columns FROM such and such table WHERE the parameter of such and such column is equal to the value.1. Selects ALL data in the tbl_name table.
SELECT * FROM tbl_name;
2. Displays the number of records in the tbl_name table.
SELECT count(*) FROM tbl_name;
3. Selects (SELECT) from (FROM) table tbl_name limit (LIMIT) 3 records, starting from 2.
SELECT * FROM tbl_name LIMIT 2,3;
4. Selects (SELECT) ALL (*) records from (FROM) table tbl_name and sorts them (ORDER BY) by the id field in order.
SELECT * FROM tbl_name ORDER BY id;
5. Selects (SELECT) ALL records from (FROM) the tbl_name table and sorts them (ORDER BY) by the id field in REVERSE order.
SELECT * FROM tbl_name ORDER BY id DESC;
6. Selects ( SELECT) ALL (*) records from ( FROM) tables users and sorts them ( ORDER BY) on the field id in ascending order, limit ( LIMIT) first 5 entries.
SELECT * FROM users ORDER BY id LIMIT 5;
7. Selects all records from the table users, where is the field fname corresponds to the value Gena.
SELECT * FROM users WHERE fname="Gena";
8. Selects all records from the table users, where the field value fname begin with Ge.
SELECT * FROM users WHERE fname LIKE "Ge%";
9. Selects all records from the table users, Where fname ends with na, and sorts the records in ascending order of value id.
SELECT * FROM users WHERE fname LIKE "%na" ORDER BY id;
10. Selects all data from columns fname, lname from the table users.
SELECT fname, lname FROM users;
11.
Let's say you have a country in your user data table. So, if you want to display ONLY a list of occurring values (so that, for example, Russia is not displayed 20 times, but only once), then we use DISTINCT. It will bring Russia, Ukraine, Belarus out of the mass of repeating values. Thus, from the table users speakers country ALL UNIQUE values will be output
SELECT DISTINCT country FROM users;
12. Selects ALL row data from the table users Where age has the values 18,19 and 21.
SELECT * FROM users WHERE age IN (18,19,21);
13.
Selects MAXIMUM value age in the table users. That is, if you have the largest value in your table age(from English age) is 55, then the query result will be 55.
SELECT max(age) FROM users;
14. Select data from table users by fields name And age WHERE age takes the smallest value.
SELECT name, min(age) FROM users;
15. Select data from table users on the field name WHERE id NOT EQUAL TO 2.
SELECT name FROM users WHERE id!="2";
3. Simple INSERT (new entry) queries
INSERT– a query that allows you to INITIALLY insert a record into the database. That is, it creates a NEW record (line) in the database.1. Does new entry in the table users, in field name inserts Sergey, and in the field age inserts 25. Thus, it is added to the table new line with these values. If there are more columns, then the remaining ones will remain either empty or with default values.
INSERT INTO users (name, age) VALUES ("Sergey", "25");
4. Simple UPDATE queries to the MySQL database
UPDATE– a request that allows you to RESERVE field values or ADD something to an already existing line in the database. For example, there is a ready-made line, but the age parameter needs to be rewritten in it, since it has changed over time.1. In the table users age becomes 18.
UPDATE users SET age = "18" WHERE id = "3";
2.
Everything is the same as in the first request, it just shows the request syntax, where two or more fields are overwritten.
In the table users WHERE id is equal to 3 field value age turns 18 and country Russia.
UPDATE users SET age = "18", country = "Russia" WHERE id = "3";
5. Simple DELETE (delete record) queries to the MySQL database
DELETE– a query that deletes a row from a table.1. Removes a row from the table users WHERE id equals 10.
DELETE FROM users WHERE id = "10";
6. Simple DROP (delete table) queries to a MySQL database
DROP– a query that deletes a table.1. Deletes the entire table tbl_name.
DROP TABLE tbl_name;
7. Complex queries to the MySQL database
Interesting queries that can be useful even for experienced usersSELECT id,name,country FROM users,admins WHERE TO_DAYS(NOW()) - TO_DAYS(registration_date)<= 14 AND activation != "0" ORDER BY registration_date DESC;
This complex query SELECTS columns id,name,country IN TABLES users,admins WHERE registration_date(date) not older 14
days I activation NOT EQUAL 0
, Sort by registration_date in reverse order (new first).
UPDATE users SET age = "18+" WHERE age = (SELECT age FROM users WHERE male = "man");
Above is an example of the so-called request within a request in SQL. Update age among users to 18+, where gender is male. I do not recommend such request options. From personal experience I will say that it is better to create several separate ones - they will be processed faster.
8. MySQL and PHP database queries
In MySQL queries in a PHP page, you can insert variables as compared values, etc. A couple of examples1. Selects all records from the table users, where is the field fname corresponds to the value of the variable $name.
SELECT * FROM users WHERE fname="$name";
2. In the table users WHERE id is equal to 3 field value age changes to the value of the $age variable.
UPDATE users SET age = "$age" WHERE id = "3";
Attention! If you are interested in any other example, then write a question in the comments!
How to optimize MySQL queries?
For an ordinary, not particularly visited site, there is not much difference whether MySQL queries to the database are optimized or not. But for production servers under heavy load, the difference between correct and incorrect SQL is huge, and at runtime they can significantly affect the behavior and reliability of services. In this article, I'll look at how to write fast queries and the factors that make them slow.
Why MySQL?
Today there is a lot of talk about Dig Data and other new technologies. NoSQL and cloud solutions are great, but a lot of popular software (such as WordPress, phpBB, Drupal) still runs on MySQL. Migration to the latest solutions can result in more than just changing the configuration on the servers. In addition, the efficiency of MySQL is still at the level, especially the Percona version.
Don't make the common mistake of throwing more and more hardware at solving the problem of slow queries and high server load - it's better to go to the root of the problem. Increasing the power of processors and hard drives and adding RAM is also a certain type of optimization, however, this is not what we will talk about in this article. Also, by optimizing the site and solving the problem with hardware, the load will only grow exponentially. Therefore, this is only a short-term solution.
A good understanding of SQL is the most important tool for a web developer; it will allow you to effectively optimize and use relational databases. In this article we will focus on a popular open source database often used in conjunction with PHP, MySQL.
Who is this article for?
For web developers, database architects and developers, and system administrators familiar with MySQL. If you haven't used MySQL before, this article may not be of much use to you, but I will still try to be as informative and helpful as possible, even for those new to MySQL.
Backup first
I recommend doing the following steps based on the MySQL you are working with, but be sure to make a backup. If you don't have a database to work with, I will provide examples for creating your own database where appropriate.
Making MySQL backups is easy using the mysqldump utility:
$ mysqldump myTab > myTab-backup.sql You can learn more about mysqldump.
What makes a query slow?
Here is a general list of factors affecting the speed of requests and server load:
- table indexes;
- WHERE condition (and use of internal MySQL functions, for example, such as IF or DATE);
- sort by ORDER BY;
- frequent repetition of identical requests;
- type of data storage mechanism (InnoDB, MyISAM, Memory, Blackhole);
- not using the Percona version;
- server configurations (my.cnf / my.ini);
- large data outputs (more than 1000 rows);
- unstable connection;
- distributed or cluster configuration;
- Poor table design.
What are indexes?
Indexes are used in MySQL to search for rows with specified column values, such as with the WHERE clause. Without indexes, MySQL must, starting with the first row, read the entire table looking for relevant values. The larger the table, the higher the costs.
If the table has indexes on the columns that will be used in the query, MySQL will quickly find the locations necessary information without viewing the entire table. This is much faster than searching each line sequentially.
Unstable connection?
When your application connects to a database and a stable connection is configured, it will be used every time without having to open a new connection each time. This optimal solution for the work environment.
Reducing the frequent repetition of identical requests
The fastest and effective method The solution I've found for this is creating a store of queries and their execution results using Memcached or Redis. With Memcache you can easily cache the result of your query, for example like this:
connect("localhost",11211); $cacheResult = $cache->get("key-name"); if($cacheResult)( //don't need a query $result = $cacheResult; ) else ( //run your query $mysqli = mysqli("p:localhost","username","password","table"); //add p: for long-term storage $sql = "SELECT * FROM posts LEFT JOIN userInfo using (UID) WHERE posts.post_type = "post" || posts.post_type = "article" ORDER BY column LIMIT 50"; $result = $mysqli->query($sql); $memc->set("key-name", $result->fetch_array(), MEMCACHE_COMPRESSED,86400); ) //Password $cacheResult to template $template->assign(" posts", $cacheResult); ?> Now a heavy query using LEFT JOIN will be executed only once every 86,400 seconds (that is, once a day), which will significantly reduce the load on the MySQL server, leaving resources for other connections.
Note: Add p: at the beginning of the MySQLi host argument to create a persistent connection.
Distributed or clustered configuration
When the data becomes more and more, and the speed of your service goes downhill, panic can take over you. A quick solution could be resource distribution (sharding). However, I do not recommend doing this unless you have good experience, since distribution inherently makes data structures complex.
Weak Table Design
Creating database schemas is not difficult work if you follow the golden rules of working with constraints and knowing what will work. For example, storing images in BLOB cells is very confusing - storing the file path in a VARCHAR cell is a much better solution.
Ensuring proper design for desired use is paramount in creating your application. Store different data in different tables (for example, categories and articles) and make sure that many to one and one to many relationships can be easily associated with IDs. Using FOREIGN KEY in MySQL is ideal for storing cascading data in tables.
When creating a table, remember the following:
- Create effective tables to solve your problems, rather than filling tables with unnecessary data and relationships.
- Don't expect MySQL to execute your business logic or programming - the data should be ready for your scripting language to insert a row. For example, if you need to sort a list in random order, do it in PHP array without using ORDER BY from the MySQL arsenal.
- Use UNIQUE index types for unique data sets and use ON DUPLICATE KEY UPDATE to keep the date updated, for example to know when a row was in last time changed.
- Use type data INT to store integers. If you don't specify a data type size, MySQL will do it for you.
To optimize effectively, we must apply three approaches to your application:
- Analysis (logging slow queries, studying the system, query analysis and database design)
- Execution requirements (how many users)
- Technology limitations (iron speed, misuse MySQL)
The columns, as you can see, store important information about the request. The columns you should pay the most attention to are possible_keys and Extra.
The possible_keys column will show the indexes that MySQL had access to in order to execute the query. Sometimes you need to assign indexes to make your query run faster. The Extra column will indicate whether an additional WHERE or ORDER BY was used. The most important thing to notice is whether Using Filesort is in the output.
What Using Filesort does is stated in the MySQL help:
MySQL has to do an extra pass to figure out how to return the rows in sorted form. This sort takes place over all rows according to the join type and stores the sort key and row pointer for all rows that match the WHERE clause. The keys are sorted and the rows are returned in the correct order.An extra pass will slow down your application and should be avoided at all costs. Another critical result of Extra that we should avoid is Using temporary. It says that MySQL had to create a temporary table to execute the query. Obviously it's terrible using MySQL. In this case, the query result should be stored in Redis or Memcache and not be executed by users again.
To avoid the problem with Using Filesort we must make sure that MySQL uses INDEX. There are currently several keys specified in possible_keys from which to choose, but MySQL can only select one index for the final query. Also, indexes can be composed of several columns, and you can also enter hints for the MySQL optimizer, pointing to the indexes that you have created.
Index hinting
The MySQL optimizer will use statistics based on table queries to select the best index to run the query on. It operates quite simply, based on built-in statistical logic, so having several options, it does not always work right choice without the help of hinting. To ensure that the correct (or incorrect) key was used, use the FORCE INDEX, USE INDEX, and IGNORE INDEX keywords in your query. You can read more about index hinting in MySQL Help.
To display the table keys, use the SHOW INDEX command. You can specify several hints for use by the optimizer.
In addition to EXPLAIN, there is the DESCRIBE keyword. With DESCRIBE, you can view table information as follows:
Adding an index
To add indexes in MySQL, you must use the CREATE INDEX syntax. There are several types of indexes. FULLTEXT is used for full-text search, and UNIQUE is used to store unique data.
To add an index to your table, use the following syntax:
Mysql> CREATE INDEX idx_bookname ON `books` (bookname(10)); This will create an index on the books table that will use the first 10 letters of the column that stores book titles and is of type varchar. In this case, any search with a WHERE query on a book title with a match of up to 10 characters will produce the same result as scanning the entire table from start to finish.
Composite indices
Indexes have a big impact on the speed of query execution. Just assigning a master unique key is not enough - composite keys are a real area of application in MySQL setup, which sometimes requires some A/B testing using EXPLAIN.
For example, if we need to reference two columns in the condition of a WHERE clause, a composite key would be an ideal solution.
Mysql> CREATE INDEX idx_composite ON users(username, active); Once we have created a key based on the username column, which stores the user's name and active columns of type ENUM, which determines whether his account is active. Now everything is optimized for a query that will use WHERE to find a valid username with active account(active = 1).
How fast is your MySQL?
Let's turn on profiling to take a closer look at MySQL queries. This can be done by running the command set profiling=1, after which you need to run show profiles to view the result.
If you are using PDO, run the following code:
$db->query("set profiling=1"); $db->query("select headline, body, tags from posts"); $rs = $db->query("show profiles"); $db->query("set profiling=0"); // disable profiling after query execution $records = $rs->fetchAll(PDO::FETCH_ASSOC); // get profiling results $errmsg = $rs->errorInfo(); //Catch some errors here The same can be done using mysqli:
$db = new mysqli($host,$username,$password,$dbname); $db->query("set profiling=1"); $db->query("select headline, body, tags from posts"); if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close(); ) if ($result = $db->query("show profile for query 1", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close( ); ) $db->query("set profiling=0"); This will return you profiled data containing the query execution time in the second element of the associative array.
Array(3) ( => string(1) "1" => string(10) "0.00024300" => string(17) "select headline, body, tags from posts" ) This query took 0.00024300 seconds to complete. It's pretty fast, so let's not worry. But when the numbers get big, we have to look deeper. Go to your application to practice with a working example. Check the DEBUG constant in your database configuration, and then start exploring the system by enabling profiling output using the var_dump or print_r functions. This way you can move from page to page in your application, getting convenient system profiling.
Full audit of your website database
To make a full audit of your requests, enable logging. Some website developers worry that logging greatly impacts execution and further slows down requests. However, practice shows that the difference is insignificant.
To enable logging in MySQL 5.1.6 use the log_slow_queries global variable, you can also mark a file for logging using the slow_query_log_file variable. This can be done by running the following query:
Set global log_slow_queries = 1; set global slow_query_log_file = /dev/slow_query.log; You can also specify this in the /etc/my.cnf or my.ini configuration files of your server.
After making changes, do not forget to restart the MySQL server with the necessary command, for example service mysql restart if you are using Linux.
In MySQL versions after 5.6.1, the log_slow_queries variable is deprecated and slow_query_log is used instead. Also, for more convenient debugging, you can enable table output by setting the log_output variable to TABLE, however, this function is only available since MySQL 5.6.1.
Log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1; The long_query_time variable specifies the number of seconds after which the query is considered slow. The value is 10 and the minimum is 0. You can also specify milliseconds using a fraction; now I indicated one second. And now every request that will be executed for more than 1 second is recorded in the logs in the table.
Logging will be done in the mysql.slow_log and mysql.general_log tables of your MySQL databases data. To turn off logging, change log_output to NONE.
Logging on a production server
On a production server that serves clients, it is better to use logging only for a short period and to monitor the load so as not to create unnecessary load. If your service is overloaded and immediate attention is needed, try isolating the problem by running SHOW PROCESSLIST, or accessing the information_schema.PROCESSLIST table by running SELECT * FROM information_schema.PROCESSLIST;.
Logging all requests on the production server can give you a lot of information and become good remedy for research purposes when checking a project, but logs for long periods will not give you much useful information compared to logs over a period of up to 48 hours (try to track peak loads to have a chance to better investigate query execution).
Note: If you have a site that experiences waves of traffic and little to no traffic at times, such as a sports site during the off season, then use this information to build and study logging.
Logging multiple requests
Not only is it important to be aware of queries that take longer than a second to execute, but you also need to be aware of queries that are executed hundreds of times. Even if queries are executed quickly, in a busy system they can consume all resources.
This is why you always need to be on guard after making changes in a live project - this is the most critical time for the operation of any database.
Hot and cold cache
The number of requests and server load have a strong impact on execution and can also affect the execution time of requests. When developing, you should make it a rule that each request should take no more than a fraction of a millisecond (0.0xx or faster) to complete on a free server.
Using Memcache has a strong effect on the load on servers and will free up resources that execute requests. Make sure you are using Memcached effectively and have tested your application with a hot cache (loaded data) and a cold cache.
To avoid running on a production server with an empty cache, it is a good idea to have a script that collects all the necessary cache before starting the server, so that a large influx of clients does not reduce the system boot time.
Fixing Slow Queries
Now that logging is set up, you might have found some slow queries on your site. Let's fix them! As an example, I will show several common problems, and you can see the logic for fixing them.
If you haven't found a slow query yet, check your long_query_time settings if you use this logging method. Otherwise, after checking all your profiling queries (set profiling=1), make a list of queries that take more time than a fraction of a millisecond (0.000x seconds) and start from there.
Common problems
Here are the six most common problems I've found optimizing MySQL queries:
ORDER BY and filesort
Preventing filesort is sometimes not possible due to the ORDER BY clause. For optimization, store the result in Memcache, or perform sorting in your application logic.
Using ORDER BY with WHERE and LEFT JOIN
ORDER BY makes queries very slow. If possible, try not to use ORDER BY. If you need sorting, then use sorting by indexes.
Using ORDER BY on temporary columns
Just don't do it. If you need to combine results, do it in your application logic; Do not use filtering or sorting on the MySQL query temporary table. This requires a lot of resources.
Ignoring FULLTEXT index
Using LIKE is the best way to make full text search slow.
Unnecessarily selecting a large number of rows
Forgetting about LIMIT in your query can greatly increase the time it takes to fetch from the database, depending on the size of the tables.
Excessive use of JOIN instead of creating composite tables or views
When you use more than three or four LEFT JOIN operators in a single query, ask yourself: is everything correct here? Continue unless you have a good reason, for example - the query is not used often for output in the admin panel, or the output result can be cached. If you need to perform a query with a large number of table join operations, then it is better to think about creating composite tables from the necessary columns or using views.
So
We discussed the basics of optimization and the tools needed to get the job done. We examined the system using profiling and the EXPLAIN statement to see what was going on with the database and how we could improve the design.
We also looked at some examples and classic pitfalls you can fall into when using MySQL. By using index hinting, we can ensure that MySQL will select the necessary indexes, especially when multiple selections are made on the same table. To continue studying the topic, I advise you to look towards the Percona project.
Database operations very often become a bottleneck when implementing a web project. Optimization issues in such cases concern not only the database administrator. Programmers need to properly structure tables, write optimal queries and more productive code. This article provides a small list of techniques for optimizing work with MySQL for programmers.
1. Optimize your queries for the query cache.
Most MySQL servers use query caching. This is one of effective methods performance improvements that are performed by the database engine in the background. If a query is executed many times, the cache begins to be used to obtain the result and the operation is completed much faster.
The problem is that it is so simple and at the same time hidden from the developer, and most programmers ignore such a great opportunity to improve the performance of the project. Some actions may actually prevent the query cache from being used during execution.
// Query cache NOT WORKING $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // The query cache is WORKING! $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= "$today"");
The reason the query cache doesn't work in the first case is because of the use of the function CURDATE(). This approach is used for all non-deterministic functions, for example, NOW(), RAND(), etc. Since the function's return result may change, MySQL decides not to cache the query. All that is needed to fix the situation is to add an extra line of PHP code before the request.
2. Use EXPLAIN for your SELECT queries
Using the EXPLAIN keyword can help paint a picture of what MySQL is doing to complete your query. This picture makes it easy to identify bottlenecks and other problems in queries or table structure.
The result of an EXPLAIN query shows which indexes are used, how the table is scanned and sorted, and so on.
Let's take a SELECT query (preferably a complex one, with a JOIN), and add the EXPLAIN keyword before it. You can use PhpMyAdmin for this. Such a query will output the result in a nice table. Let's say we forgot to add an index on a column that is used for a JOIN:
After adding an index for the group_id field:
Now instead of scanning 7883 rows, only 9 and 16 rows from two tables will be scanned. A good way to evaluate performance is to multiply all the numbers in the “rows” column. The result is approximately proportional to the amount of data being processed.
3. Use LIMIT 1 if you need to get a unique string
Sometimes, while using a query, you already know that you are looking for only one row. You can get a unique record or simply check for the existence of any number of records that satisfy the WHERE clause.
In such a case, adding LIMIT 1 to your query may improve performance. Under this condition, the database engine stops scanning records as soon as it finds one and does not scan the entire table or index.
// Is there any user from Alabama? // Don't do this: $r = mysql_query("SELECT * FROM user WHERE state = "Alabama""); if (mysql_num_rows($r) > 0) ( // ... ) // This will be much better: $r = mysql_query("SELECT 1 FROM user WHERE state = "Alabama" LIMIT 1"); if (mysql_num_rows($r) > 0) ( // ... )
4. Index your search fields
Index more than just primary and unique keys. If any columns in your table are used for search queries, then they need to be indexed.
As you can see, this rule also applies to searching by part of a string, for example, “last_name LIKE ‘a%’”. When the start of a string is used for a search, MySQL can use the index of the column being searched.
You should also understand what types of searches you can't use regular indexing for. For example, when searching for the word (“WHERE post_content LIKE ‘%apple%’”), the benefits of indexing will not be available. In such cases, it is better to use mysql full-text search or build your own solutions based on indexing.
5. Indexing and using the same types for linked columns
If your application contains many JOIN queries, you need to index the columns that are linked in both tables. This has an effect on internal optimization bind operations in MySQL.
Also, the columns being linked must be of the same type. For example, if you associate a DECIMAL column with an INT column from another table, MySQL will not be able to use the index on at least for one of the two tables. Even the character encoding must be the same for identical string type columns.
// Search for a company from a specific state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id"); // both columns for the state name must be indexed // and both must be the same type and character encoding // or MySQL will perform a full table scan
6. Don't use ORDER BY RAND()
This is one of those tricks that looks cool, and many new programmers fall into its trap. They cannot even imagine what a terrible problem they are creating for themselves by starting to use this expression in their queries.
If you really need to randomize the rows in the result of your query, then there are many the best ways solve such a problem. Of course this will be implemented additional code, but you will be saved from a problem that grows exponentially with the volume of data. The thing is, MySQL performs a RAND() operation (which takes up CPU time) on every single row in the table before sorting it and giving you just one row.
// DO NOT do this: $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1"); // This will work better: $r = mysql_query("SELECT count(*) FROM user"); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d - 1); $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
This way you get a random number that is less than the number of rows in the query result and use it as an offset in the LIMIT clause.
7. Avoid using SELECT *
The more data is read from the table, the slower the query will be. Such operations also take time to complete disk operations. And if the database server is separate from the web server, then delays will also be caused by data transfer over the network between the servers.
It's a good habit to specify a column when doing a SELECT.
// Bad: $r = mysql_query("SELECT * FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc($r); echo "Welcome ($d["username"])"; // This is better: $r = mysql_query("SELECT username FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc($r); echo "Welcome ($d["username"])"; // The difference becomes significant with large amounts of data
8. Try to use the id field everywhere
A good practice is to use an id field in each table that has the PRIMARY KEY, AUTO_INCREMENT properties set, and is of type from the INT family. Preferably - UNSIGNED, since in this case the value cannot be negative.
Even if your table has a field with a unique username, don't make it the primary key. VARCHAR fields are slow to work as primary keys. Also, the structure of your database will be better if it internally uses links to records based on id.
In addition, the MySQL engine uses primary keys for its internal tasks, and the use of the id field creates optimal conditions for solving them.
One possible exception to this rule is “associative tables,” which are used for many-to-many relationships between two other tables. For example, the “posts_tags” table contains 2 columns: post_id, tag_id. They are used to describe the relationship between two tables “post” and “tags”. The table described may have a primary key that contains both id fields.
9. Use ENUM instead of VARCHAR
// Create a prepared expression if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) ( // Bind the parameters $stmt->bind_param("s", $state); // Execute $stmt->execute(); // Bind the result variables $stmt->bind_result($username); // Get the values $stmt->fetch(); printf("%s is from %s\n", $username , $state); $stmt->close(); )13. Unbuffered requests
Typically, when you execute a request from a script, the script is interrupted until the request is completed. This behavior can be changed using unbuffered queries.
Excellent explanation of the mysql_unbuffered_query() function from the PHP documentation:
“mysql_unbuffered_query() sends an SQL query to MySQL server without automatic receipt and buffering the result rows as the mysql_query() function does. This way, a certain amount of memory is saved by SQL queries that produce a large result set, and you can start working on the result set immediately after receiving the first row, without waiting for the SQL query to be completely executed.”
However, there are several limitations. You must either read all rows or call mysql_free_result() before executing the next query. You also cannot use mysql_num_rows() or mysql_data_seek() to set the result.
14. Store the IP address as UNSIGNED INT
Many programmers create a VARCHAR(15) field to store an IP address, without even thinking about the fact that they will store an integer value in this field. If you use INT, the field size will be reduced to 4 bytes, and it will have a fixed length.
You must use the UNSIGNED INT type because the IP address uses all 32 bits of an unsigned integer.
$r = "UPDATE users SET ip = INET_ATON("($_SERVER["REMOTE_ADDR"])") WHERE user_id = $user_id";
15. Tables with a fixed record length (Static) are faster
When each individual column in a table has a fixed length, then the table as a whole is considered “static” or “fixed record length”. Examples of column types that do not have a fixed length: VARCHAR, TEXT, BLOB. If you include at least one column of this type, the table will no longer be considered "static" and will be processed differently by the MySQL engine.
"static" tables are processed faster by the MySQL engine when searching for records. When you need to read a specific record in a table, its position is quickly calculated. If the row size is not fixed, then determining the position of the record requires time to search and compare with the index of the main key.
Such tables are also easier to cache and easier to recover from failures. But they can take up more space. For example, if you convert a VARCHAR(20) field to a CHAR(20) field, then 20 bytes will always be occupied, regardless of whether they are in use or not.
Using the Vertical Splitting technique makes it possible to separate variable-length columns into a separate table.
16. Vertical separation
Vertical partitioning is the act of dividing a table structure vertically for the purpose of optimization.
Example 1: You have a table that contains home addresses that are rarely used in the application. You can split your table and store the addresses in a separate table. This will reduce the size of the main user table. And as you know, a smaller table is processed faster.
Example 2: You have a field “last_login” in your table. It is updated every time a user registers on the site. But every table update causes the query to be cached, which can create system overload. You can separate this field into another table to make updates to the user table less frequent.
But you need to be sure that you don't need to permanently link the two tables you just split, as this could lead to poor performance.
17. Separate large DELETE or INSERT queries
If you need to perform big request DELETE or INSERT on a live site, you need to be careful not to disrupt traffic. When a large query is running, it can lock your tables and cause your application to stop.
Apache runs many parallel processes/threads. for this reason it works more efficiently when the script finishes executing as quickly as possible, so the server doesn't use too much open connections and processes that consume resources, especially memory.
If you lock tables for an extended period of time (for example, 30 seconds or more) on a heavily loaded web server, you can cause a backlog of processes and requests that will require significant cleanup time or even cause your web server to stop.
If you have a script that removes a large number of records, just use the LIMIT clause to split it into small batches to avoid the situation described.
While (1) ( mysql_query("DELETE FROM logs WHERE log_date<= "2009-10-01" LIMIT 10000"); if (mysql_affected_rows() == 0) { // выполняем удаление break; } // вы можете сделать небольшую паузу usleep(50000); }
18. Small columns are processed faster
For a database engine, disk is the most important bottleneck. The desire to make everything smaller and smaller usually has a positive impact on productivity by reducing the amount of data being moved.
The MySQL documentation contains a list of data storage standards for all types.
If the table will only contain a few rows, then there is no reason to make the primary key type INT rather than MEDIUMINT, SMALLINT, or even TINYINT. if you only need the date, use DATE instead of DATETIME.
You just need to remember about the opportunities for growth.
19. Choose the right storage mechanism
There are two main data storage engines for MySQL: MyISAM and InnoDB. Each has its own advantages and disadvantages.
MyISAM is great for read-heavy applications, but it doesn't scale well when there are a lot of records. Even if you update one field in one row, the entire table will be locked and no process will be able to read anything until the query completes. MyISAM performs calculations quickly for queries like SELECT COUNT(*).
InnoDB is a more complex storage engine, and it can be slower than MyISAM for most small applications. But it supports row locking, which is better for scaling tables. It also supports some additional features such as transactions.
20. Use object-relational mapping
Using an object-relational mapping (ORM - Object Relational Mapper) provides a number of advantages. Everything that can be done in an ORM can be done manually, but with more effort and higher developer level requirements.
ORM is great for lazy loading. This means that values can be retrieved when they are needed. But you need to be careful because you can create a lot of small queries that will reduce performance.
An ORM can also combine your queries into transactions, which are significantly faster than individual database queries.
For PHP you can use ORM Doctrine.
21. Be careful with persistent connections
Persistent connections are designed to reduce the cost of restoring connections to MySQL. When a persistent connection is created, it remains open even after the script completes. Since Apache reuses child processes, the process is executed for the new script, and it also uses the MySQL connection.
This sounds great in theory. But in reality, this feature is not worth a penny due to the problems. It can cause serious problems with connection limits, memory overflow, and so on.
Apache works on concurrency principles and creates many child processes. This is the reason why persistent connections do not work as expected on this system. Before using the mysql_pconnect() function, consult your system administrator.