* - available in FlySpeed SQL Query Tool Standard version only. Buy now Download Feature list. FlySpeed Database Query Tool was successfully tested with the following databases. Microsoft SQL Server Oracle MySQL Microsoft Jet 4 Engine: MS Access, MS Excel * PostgreSQL InterBase, Firebird IBM DB2 (Universal, AS/400) Informix Sybase ASA, ASE. Query Profiler keeps the query text along with its profiling results to let you optimize MySQL queries effectively. Your move is to select a required profiling result and click the SQL Query button. With query changes history you can come back to any step of the query optimization, review, execute or save the query.
This article is also available as a TechRepublic, which includes the code listings in a more manageable text file format. Any database programmer will tell you that in high-traffic database-driven applications, a single badly-designed can significantly impact the overall performance of your application. Not only does such a query consume more database time than it ideally should, but it can have an exponential effect on the performance of other application components.
Optimizing query performance is as much a black art as a science, as heavily dependent on the developer's intuition as on hard statistical performance data. Fortunately, databases likes come with some tools to aid the process, and this article discusses three of them briefly: using indexes, analyzing queries with EXPLAIN, and adjusting MySQL's internal configuration.
#1: Using indexes MySQL allows you to index database tables, making it possible to quickly seek to records without performing a full table scan first and thus significantly speeding up query execution. You can have up to 16 indexes per table, and MySQL also supports multi-column indexes and full-text search indexes. Here, indexing the username field of the users table ensures that SELECT queries which reference this field in their WHERE or HAVING clause will run a little faster than in the pre-indexed state.
You can check that the index was created ( Listing B) with the SHOW INDEX command: Listing B mysql SHOW INDEX FROM users; ———————+——————-+—————-+——————-+—————+————+———+——————+————-+ Table Nonunique Keyname Seqinindex Columnname Collation Cardinality Subpart Packed Null Indextype Comment ———————+——————-+—————-+——————-+—————+————+———+——————+————-+ users 1 idxusername 1 username A NULL NULL NULL YES BTREE ———————+——————-+—————-+——————-+—————+————+———+——————+————-+ 1 row in set (0.00 sec) It's important to note that indexes are a double-edged sword. Indexing every field of a table is usually unnecessary, and is quite likely to slow things down significantly when inserting or updating data because of the additional work MySQL has to do to rebuild the index each time. On the other hand, avoiding indexes altogether isn't such a great idea either, because while this will speed up INSERTs, it will cause SELECT operations to slow down. There is thus always a trade-off to be made, and it's wise to consider what the primary function of the table will be (data retrieval or data edit) when designing the indexing system.
#2: Optimizing query performance When analyzing query performance, it's also useful to consider the EXPLAIN keyword. This keyword, when placed in front of a SELECT query, describes how MySQL intends to execute the query and the number of rows it will need to process to successfully deliver a result set.
MySQL Query Browser for Windows is a powerful tool to create, modify, execute and optimize SQL statements using a graphical control panel. This graphical environment includes a number of features that make it special: - History of executed SQL requests - Editor for manually creating or editing SQL statements - Results in a new window to run multiple queries and compare results - Database Explorer to select, modify or delete columns or tables - Instant help for selecting objects, parameters or functions.