TOP MySQL GUI Tools: February 2010

Sunday, February 28, 2010

MySQL GUI. The solution for Linux and Mac OS.

Thanks to the plug-plug SQuirreL SQL Client (www.squirrelsql.org) supports a variety of databases (Oracle, MySQL, PostgreSQL, IBM DB2 - all over 20). Actually to get started you must select the appropriate driver (plugin) and create a so-called alias - set the settings for connecting to the server. But the most important chip in the fact that the squirrel client written in Java and thus perfect start to any platform: as a Wind, and Linux and macos. However, universality - it is not the only horse. In addition to a nice spreadsheet, in prog implemented several visual tools with which, for example, we can construct a graph showing the relationship between the tables. Very high quality made and query editor in the arsenal of which there are syntax highlighting, and a built-in IntelliSense (just press press Ctrl + Space to autocomplete table names or teams) to accelerate the process of writing queries. To quickly view a list of all available options, just press Ctrl + t. Moreover, the editor supports templates.

When we talk about a tool to manage the database MySQL, is a priori believe that it will be phpMyAdmin. At any hosting phpMyAdmin - de-facto standard. Bad one - this rather auxiliary tools are often used as a basic Mysql GUI tool. But when constantly dealing with a database, then the tool must be chosen properly.

No, against the phpMyAdmin I have nothing, but let's judge for sober. Implementation in PHP allows you to use the solution virtually anywhere, but immediately imposes severe restrictions on the ease of use. What are fully reloads the page in the absence of AJAX. Working through such an interface provides a tight, and editing data in general is maddening. Have you ever accidentally pressed the button "Delete page", although only wanted to delete one entry? I - pressed. In addition, phpMyAdmin must configure each server separately. On access to different servers from one place can only dream. Yes, the implementation of a Web-based applications a plus in some situations, but for designing databases, data editing, programming stored procedures and complex SQL queries is a much more successful solutions.

MySQL GUI. Best Tool for MySQL. What's New? Video Article.

What's new in Navicat Premium?

Navicat Premium is a multi-connections Database Administration tool allowing you to connect to MySQL, PostgreSQL and Oracle databases simultaneously within a single application, making database administration to multiple kinds of database so easy. More video...

MySQL LIKE

Doing a search through data is easy enough when the equations produce a definite yes or no situation. There may be times though you will want to perform a more general search.

The LIKE and NOT LIKE have two search helper symobls. The underscore _ character that looks for one character and the percentage % character that looks for zero or more characters.

$sql = mysql_query("SELECT * FROM table_name WHERE columnname LIKE value%");

while ($row = mysql_fetch_row($sql)) {
echo "$row[0] $row[1] $row[2]
";
}

The query will only pick out the rows that provide a TRUE result according to the WHERE equation. The equation will equal the LIKE VALUE plus some possible extra characters afterwards. Example...

Download SAKILA sample MySQL database

Learn MySQL With Sakila and MySQL GUI tool

Obtain the example Sakila database from the MySQL website to examine a professionally designed database and use a GUI Front End for MySQL instead of the command line.

There is no doubt MySQL stands out as one of many open source relational database management systems (RDBMS) that are available on the market. It can be downloaded and installed by itself or as part of a software bundle such as LAMP or WAMP (MySQL is the M in LAMP or WAMP). Once its installed, the next question is what to do with it?

Get Sakila From the MySQL Site

An excellent method for becoming familiar with both basic SQL principles as well as differences in syntax unique to an actual database like MySQL is to download an example database. From the MySQL website, find the Example Databases section and download the Sakila database in either TGZ or Zip format. Download.

The Sakila example comes with three files. Run the sakila-schema.sql first to make the schema for the database, then sakila-data.sql to populate the database. (Be sure to add the MySQL binaries to the PATH system variables. Otherwise, it will be necessary to change directory to where mysqladmin.exe and mysql.exe are and use full path to where the extracted Sakila files are located).

To create a database from the command line type:

C:\tmp\sakila-db>mysqladmin -uroot -p create sakila

Thursday, February 25, 2010

Top 1000 SQL Performance Tips

Interactive session from MySQL Camp I:

Specific Query Performance Tips (see also database design tips for tips on indexes):

1. Use EXPLAIN to profile the query execution plan
2. Use Slow Query Log (always have it on!)
3. Don't use DISTINCT when you have or could use GROUP BY
4. Insert performance
1. Batch INSERT and REPLACE
2. Use LOAD DATA instead of INSERT
5. LIMIT m,n may not be as fast as it sounds
6. Don't use ORDER BY RAND() if you have > ~2K records
7. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
8. Avoid wildcards at the start of LIKE queries
9. Avoid correlated subqueries and in select and where clause (try to avoid in)
10. No calculated comparisons -- isolate indexed columns
11. ORDER BY and LIMIT work best with equalities and covered indexes
12. Separate text/blobs from metadata, don't put text/blobs in results if you don't need them
13. Derived tables (subqueries in the FROM clause) can be useful for retrieving BLOBs without sorting them. (Self-join can speed up a query if 1st part finds the IDs and uses then to fetch the rest)
14. ALTER TABLE...ORDER BY can take data sorted chronologically and re-order it by a different field -- this can make queries on that field run faster (maybe this goes in indexing?)
15. Know when to split a complex query and join smaller ones
16. Delete small amounts at a time if you can
17. Make similar queries consistent so cache is used
18. Have good SQL query standards
19. Don't use deprecated features
20. Turning OR on multiple index fields (<5.0) into UNION may speed things up (with LIMIT), after 5.0 the index_merge should pick stuff up. 21. Don't use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you need it for the total # of rows, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() 22. Use INSERT ... ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT 23. use groupwise maximum instead of subqueries 24. Avoid using IN(...) when selecting on indexed fields, It will kill the performance of SELECT query.

Sometimes it is necessary to administer our website download files in a context where there is access only for SSH. In this case, you can use SFTP-connection for your site, which will allow to quickly upload the necessary files on the site. SFTP - SSH File Transfer Protocol (sometimes called Secure File Transfer Protocol).

In addition to SFTP file transfer makes it possible to perform additional operations: for example, download file after you hang up, or delete a file on the server and many other operations. For this reason, there are graphical and pseudographic clients for SFTP. Read more

Monday, February 22, 2010