Category — mysql

Why use mysql instead of access?

Cost. MySQL is free. Access is not. Mysql can also run on a variety of hardware and operating systems, which does not limit you to proprietary software.

Multiple-user access.
MySQL can handle many simultaneous users. It was designed from the ground up to run in a shared environment that is capable of taking on a large numbers of clients.

Management of large databases. MySQL can manage gigabytes of data, and more. This is possible in access, but not recommended.

Security. When Access tables are stored locally, anyone can run Access, and see your tables. It’s possible to assign a database a password, but many people forget to do this. When your tables are stored in MySQL, the MySQL server manages security. Anyonetrying to access your data must know the proper user
name and password.

Centralized backup location. If individual Access users each store their data locally, backups can be more complicated: 200 users means 200 table backups. While some sites address this problem through the use of network backups, others deal with it by making backups the responsibility of individual machine owners–which usually means no backups at all. Mysql allows you to have one centralized location that can be backed up on a regular basis by a system administrator or DBA.

Manually transferring your data

One way to transfer your data is export all the data from each table (using the file->export command) to a comma delimited text file (CSV). It can then be imported back into mysql using the following commands (from the mysql console client):

you could also use mysqlimport:

Applications

An application that can make the migration process much easier is called dbTools (free trial available here)

The following are five ways to improve queries involving table inserts:

1) use LOAD DATA INFILE when loading data from a text file

This is around 20 times faster than using insert statements.

2) use INSERT statements with multiple VALUES lists to insert several rows at a time

This is many times faster than using separate single-row insert statements. Tuning the bulk_insert_buffer_size variable can also make inserts (to tables that contain rows) even faster.

3) enable concurrent inserts for myisam tables

The concurrent_insert system variable can be set to modify the concurrent-insert processing. By default, the variable is set to 1. If concurrent_inserts is set to 0, concurrent inserts are disabled. If the variable is set to 2, concurrent inserts at the end of the table are allowed even for tables that have deleted rows.

4) use insert delayed

This is useful if you have clients that cannot or need not wait for the insert to complete. This is a common situation when you use MySQL for logging and you also periodically run select and update statements that take a long time to complete. When a client uses insert delayed, the server returns right away, and the row is queued to be inserted when the table is not in use by any other thread. Another benefit of using insert delayed is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

5) lock your tables before inserting (for non-transactional tables)

This benefits performance because the index buffer is flushed to disk only once, after all insert statements have completed. Normally, there would be as many index buffer flushes as there are insert statements. Explicit locking statements are not needed if you can insert all rows with a single insert.

To obtain faster insertions for transactional tables, you should use start transaction and commit instead of lock tables.

The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.

• Remove unnecessary parentheses
• COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables. This is also done for any NOT NULL expression when used with only one table.
• If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table

This command defragments a table after you have deleted a lot of rows from it.

For MyISAM tables that change frequently, you should try to avoid all variable-length columns (VARCHAR, BLOB, and TEXT). The table uses dynamic row format if it includes even a single variable-length column.

Use insert delayed when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write.

• Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
• Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is executed even if there is another client waiting.

Use multiple-row INSERT statements to store many rows with one SQL statement.

Columns with identical information in different tables should be declared to have identical data types so that joins based on the corresponding columns will be faster.

The most powerful command within mysql is explain. Explain can tell you exactly what mysql is doing when you execute a query and with this information, you can find slow queries and minimize execution time, which can significantly improve the speed of your web application.

How to use the explain command

Here is a simple example of its usage:

Database Schema:

(users table)

(address table)

In this simple example, I am selecting a record that is representing a user, based on a userid.

Here is the output:

Variable	Details
	The ID of this table in the query. EXPLAIN will create one output record for each table in the query.
	possible values: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, and DERIVED.
	The name of the table MYSQL will read the records from.
	The type of join that mysql will use. Possible values: eq_ref,ref,range, index, or all.
	a list of indexes (or NULL if none) mysql can use to find rows in the table.
	The name of the index MYSQL will use (after checking all possible indexes).
	The size of the key in bytes.
	The columns or values that are used to match against the key
	The number of rows mysql thinks it needs to examine to execute the query.
	extra information about the query

This example is pretty straight forward. Since we are peforming a search based on the primary key (userid), there can be only one record that can possible match (the rows variable is 1).

A more advanced example:

This query is more advanced than the first one. It is performing an inner join on the users and address table based on the userid. The userid is a primary key within the users table, but it is not an index in the address table. The output of the explain command shows us the following:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: all
Possible_Keys: (none)
Ref: (none)

The first table is optimized. It is using the primary key for this query. The second table, however is not optimized. The type is all and the Possible_keys=(none), which means it is going to have to go through a full table-scan. Adding an index on the user field optimizes this query.

Final output after index is added:

(users table)

Type: const
Possible_Keys: primary
Ref: const

(address table)

Type: const
Possible_Keys: primary
Ref: const

More information on this command can be found Here

It has happened to all of us running a website or application using mysql as its back-end database. Performance is suddenly very sluggish and you have no idea what is causing it. Now there may be other factors that are causing the issue (overloaded CPU, harddrive running out of space, or a lack of bandwidth), but it could also be a query that is not optimized and/or is taking much longer than it should to return.

How do you know which queries are taking the longest to execute? Mysql has built-in functionality for checking this through the slow query log.

To enable (do one of the following):

1) add this to /etc/my.cnf

log-slow-queries=/tmp/slow_queries.log
long_query_time=10

2) call mysqld with –log-slow-queries[=/tmp/slow_queries.log]

long_query_time is the maximum amount of seconds a query can take before it will be logged to the slow query log.

other related options:

–log-slow-admin-statements

Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log.

–log-queries-not-using-indexes

If you are using this option with –log-slow-queries, queries that do not use indexes are logged to the slow query log.

If slow query logging has been enabled successfully, you will see “ON” in the VALUE field for “log_slow_queries” (shown above).

Note: Queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row.

You may also run into the case where a query is slow at one time (such as when you are logging it) but not another (if you execute it manually):

• A table may be locked, causing the query to wait. the lock_time indicates how long the query waited for locks to be released
• none of the data or indexes have been cached in memory. This is common when MySQL first starts or your tables have not been optimizied
• a background process was running, making disk I/O considerably slower
• The server may have been overloaded with other unrelated queries at the same time, and there wasn’t enough CPU power to do the job efficiently

Log analysis

MySQL also comes with mysqldumpslow, a perl script that can summarize the slow query log and provide a better idea of how often each slow query executes.