Which data is to be stored in MySQL

Don't try to cram all the information you have into databases. For instance, don't store images there even though MySQL allows you to do this. Putting binary images of graphic files into the database will only slow down the work of your server. It will be much easier and economical in terms of used resources to read a file with an image from the disc than to access SQL from the script, make a query, retrieve the image, process it, and present it to a visitor of a web server providing necessary http headers. In the latter case the operation of presenting an image to a visitor will take up times as much resources of the hard drive, memory and disc. Also keep it in your mind that there are mechanisms of hashing web files that allow a user to save up on traffic, while dynamic content generation deprives your visitors of such a possibility.

So instead of storing images in MySQL you'd better use it for storing information on the basis of which there can be generated links to static images in the files that were dynamically created by scripts.

Request Optimization

In situations when you really need just a certain portion of data from MySQL you can use the LIMIT keyword for the SELECT function. It is useful when one needs to show some search results from the database. Let's say, the database contains the list of goods your web store offers. It's rather inhumane with respect to users to give out the whole list of goods under the requested category as not everyone has speedy connection channels, and giving out 100 Kb more information than needed makes users spend minutes waiting for the loading of the search results page. In this case it is better to give out information by portions, e.g. 10 positions. It's not correct to fetch all the data from the database and then to filter the output with the help of the script. It would be more optimal to make the following query:

select good, price from books limit 20,10

As a result, MySQL will give out 10 positions from the database to you, with position 20 being the first. When presenting this information to a user create the link "Next 20 goods" giving the script the next position starting with which the output of the list of goods will be done. And use this number when generating a query to MySQL.

Also, you have to remember that when generating SQL queries you should request only the information you really need. For example, if there are 10 fields in the database, and at the moment you really need just two of them, instead of the query:

select * from table_name

use a construction of the following type:

select field1, field2 from table_name

This way you will not overload MySQL, or take up more memory than needed, and you won't have to carry out additional disc operations.

You should also use the keyword WHERE when you need to retrieve information under a certain template. For instance, if you need to retrieve fields with the titles of books by Alex as the author from the database, use the following construction:

select title from books where author='Alex'

One more keyword LIKE allows you to search for fields values of which are 'like' or similar to the requested template:

select title from books where author like 'Alex%'

In this case MySQL will give out titles of books that have the value of the field 'author' starting with 'Alex'

Resource-intensive operations

At the same time you have to keep it in your mind that there are operations requiring more resources than usual requests. For instance, using the operation DISTINCT to the function SELECT takes up much more of the CPU time than just usual SELECT. DISTINCT tries to find unique values while making a lot of comparisons, substitutions and calculations. And the bigger the amount of data to which you apply DISTINCT gets (because naturally your database grows with time), the more slowly such request will be carried out. And the growth of resources required for such function will not go in direct proportion to the amount of kept and processed data, but much faster.

Indices

Indices are used to speed up the search of a field value. If you don't create an index, then MySQL carries out sequential browsing of all the fields starting with the first entry and ending up with the last one while comparing the requested value with the base one. The bigger the table is and the more fields it has, the more time the fetching will take up. While if this table has an index for the column concerned, then MySQL will be able to do positioning to the physical location of the data faster without browsing through the whole table. For example, if this is a 1000-line table, then the search speed will be 100 times faster at a minimum. This speed will even increase if there is a need to address all the 1000 columns because in this case time is not spent on hard drive positioning.

Index creation is worthwhile in the following situations:

Fast search using the construction WHERE

Search for lines from other tables while carrying out packing

Search for the MIN() or MAX() values for an indexed field

Sorting or grouping a table when an indexed field is used

In some cases there is no need to address the data file. If all the used fields of a table are digital and if they form a left-side index for some keyword, then the values can be retrieved from the index tree at a much greater speed

If there are requests of the following kind:

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

and there exists a mixed index for the fields col1 and col2, then the optimizer will try to find the most limited index by means of detecting the one which is able to find fewer lines. The optimizer will then use this index for data retrieval.

If the table has a mixed index, then any left-side match with the existing index will be used. For example, if there is a mixed index of the 3 fields (col1, col2, col3), then the index search can be carried out by the fields (col1), (col1, col2) and (col1, col2, col3).

Web Hosting Search