Home
Home Page
Krossbrauzernoe alignment on the center (table height=100 %)
Lists a la MS Word
Features of links « upwards pages »
Tables and borders
We get rid from cellpadding and cellspacing
Optimum use MySQL
Competent job with files: exclusive blocking of files
How to learn{find out} the size of a file on any web - server
Regular expressions
Processing of lines in RNR
Creation of system of the account of visitings
Tracking a content on dynamic sites
CSS: advice{councils} and receptions, a part 2
CSS: advice{councils} and receptions, a part 1
Ten rules of a spelling of a safe code on PHP
About tags and metatags
Potential vulnerability of php-scripts
Removal{Distance} of a line from a file
The generator of passwords
Links
topic
 

Optimum use MySQL

Introduction


During granting services of a hosting we pay attention on most often meeting mistakes which are made by users by development of the virtual servers. One of "heavy" places for the typical web designer is job with the MySQL-server. Studying principles of functioning SQL is usual and methods of job with databases is conducted under the literature of which get out only actual at the moment of reading a thing - how to incorporate to base, how to query, how to update the information or to add new recording in a database and so on.


Such approach, certainly, gives desirable result - interfaces of a website of the user in a result appear integrated with a database. However not always users reflect on as far as their base as it is possible to optimize occuring at job with MySQL processes optimum works and what functioning the virtual server will be at increased loading, "inflows" of users in result, for example, "promotions" of a site.


This clause{article} will help you to optimize job with SUBD MySQL. The stated material does not apply for the detailed description of optimization MySQL in general, and only pays attention on most often mistakes made by users and tells about how them to avoid. In more detail to learn{find out} about subtleties of adjustment MySQL it is possible on the specialized pages, links on which are resulted at the end of this clause{article}.

What data need to be stored{kept} in MySQL


Do not try to place in databases the information which at you is. For example, it is not necessary to store{keep} there a picture though MySQL it and allows. Placing in a database binary images of graphic files, you only slow down job of the server. To read a file with a picture from a disk it is much easier and, from the point of view of consumed resources, more economically, rather than to incorporate from a script to SQL, to query, receive an image, to process it  and, having given out the necessary http-headings to show the visitor of the web - server. In the second case operation of delivery of a picture will demand in some times more resources of the processor, memory and a disk. Also it is necessary to remember that there are mechanisms of caching of webs - documents which allow the user to save on the traffic, and at dynamic generation of a content you actually deprive with the visitors of this convenient opportunity.


Instead of pictures it is better to store{keep} in MySQL the information on the basis of which it is possible to generate links on static a picture in documents dynamically created by scripts.

Optimization of searches


In situations when really it is required to receive only the certain portion of the data from MySQL, it is possible to use key LIMIT for function SELECT. It is useful, when, for example, it is necessary to show results of search something in a database. We admit{allow}, in base there is a list of the goods which are offered by your Internet - shop. To give out the list of the goods in the necessary category a little negumanno in relation to the user - liaison channels about the Internet not at all fast and delivery superfluous hundred kilobyte of the information frequently forces users to lead{carry out} not one minute pending results of loading of page. In such situations the information give out in the portions on, we admit{allow}, 10 positions. It is wrong to do{make} sample of base of the information and kill of a conclusion by a script. Will query much more optimum a kind



select good, price from books limit 20,10


In result, MySQL "will give" you 10 recordings from base since 20-th position. Having given out result to the user, make links " the Following of 10 goods ", as parameter having passed a script the following position from which it will be judged the list of the goods, and use this number at generation of search to MySQL.


Also it is necessary to remember, that at drawing up of searches to a database (SQL queries) it is necessary to request only that information which is really necessary for you. For example, if in base of 10 fields, and at present really it is required to receive only two of them, instead of search



select * from table_name


Use a design of a kind



select field1, field2 from table_name


Thus, you will not load MySQL with unnecessary job, to borrow{occupy} superfluous memory and to make additional disk operations.


Also it is necessary to use key WHERE there where it is necessary to receive the information getting under a certain pattern. For example, if it is necessary to receive from base of a field with names of books which author is Ivanov, it is necessary to use a design of a kind



select title from books where author ='Ivanov '


Also there is key LIKE which allows to search for fields which values "are similar" to the set pattern:



select title from books where author like ' Ivanov of % '


In this case MySQL will give out names of books, values of a field author at which begin with 'Ivanov'.



Resursoemkie operations


At the same time it is necessary to remember, that there are operations which performance in itself demands the big resources, than for usual searches. For example, use of operation DISTINCT to function SELECT causes consumption of much more quantity{amount} of processor time, than usual SELECT. DISTINCT tries to search for unique values, frequently making set of comparisons, substitutions and calculations. And, the more there is a volume of the data to which it is applied DISTINCT (in fact your base in due course grows), the medlenee such search and growth of the resources required for performance such function will be carried out, will occur not directly proporconal`no to volume of the stored{kept} and processable data, and is much faster.

Indexes


Indexes use for faster search on value of one of fields. If the index is not created, MySQL carries out consecutive viewing all fields from the very first recording up to the latest, carrying out comparison of the chosen value with initial. Than more table and the more in her of fields, the is longer carried out sample. If the given table has index for a considered{an examined} column MySQL can make fast positioning to a physical arrangement of the data needlessly to carry out full viewing the table. For example, if the table will consist of 1000 lines speed of search at least in 100 times will be faster. This speed will be even higher, if there is a necessity to address at once to all 1000 stolbcam since in this case there are no expenses of time for positioning of a hard disk.


In what situations creation of an index expediently:

?         Fast search of lines at use of design WHERE

?         Search of lines from other tables at performance of association

?         Search of value MIN () or MAX () for a proindexed field

?         Sorting or a grouping of the table in case is used a proindexed field

?         In some cases necessity to address to a file of the data is completely lost. If all used fields for some table digital also form a left-hand index for some key values can be returned completely from an index tree with the much greater speed

?         If searches of a kind are carried out



SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;


Also there is a mixed index for fields col1 and col2 the data will be returned directly. If separate indexes for col1 and for col2 optimizator will try to find the most limited index by definition of what from indexes can find less lines are created, and will use this index for data acquisition.


If the table has mixed index any left-hand concurrence to an existing index will be used. For example, if is the mixed index of 3 fields (col1, col2, col3), index search can be carried out on fields (col1), (col1, col2) and (col1, col2, col3).



Support of connection


As you for certain know, for job with the MySQL-server it is necessary to establish preliminary with it  connection, having showed{presented} a login and the password. Process of installation of connection can proceed much more time, rather than direct processing of search to base after installation of connection. Following logic, it is necessary to avoid superfluous connections to base, not being disconnected from it{her} there where it can be made if further it is planned to continue job with the SQL-server. For example, if your script has established connection to base, has made sample of the data for the analysis, it is not necessary to close connection to base if during job of the same script you plan results of the analysis to place in base.


Also it is possible to support so-called persistent (constant) connection to base, but it is possible in full at use of more complex  environments of programming, than php or perl in a usual CGI-mode when the interpreter of corresponding language razovo is started by the web - server for performance of the come search.