Hi all,

Below are the tips for optimizing your MySQL installation, Queries and Websites.

Go through them in detail and these tips will surely help you to optimize your MySQL database.

What one can and should optimize

• Hardware
• OS / libraries
• SQL server (setup and queries)
• API
• Application

Optimizing hardware for MySQL

• If you need big tables ( > 2G), you should consider using 64 bit hardware like Alpha, Sparc or the upcoming IA64. As MySQL uses a lot of 64 bit integers internally, 64 bit CPUs will give much better performance.
• For large databases, the optimization order is normally RAM, Fast disks, CPU power.
  More RAM can speed up key updates by keeping most of the used key pages in RAM.
  If you are not using transaction-safe tables or have big disks and want to avoid long file checks, a UPS is good idea to be able to take the system down nicely in case of a power failure.
• For systems where the database is on a dedicated server, one should look at 1G Ethernet. Latency is as important as throughput.

Optimizing disks

• Have one dedicated disk for the system, programs and for temporary files. If you do very many changes, put the update logs and transactions logs on dedicated disks.
  Low seek time is important for the database disk; For big tables you can estimate that you will need: log(row_count) / log(index_block_length/3*2/(key_length + data_ptr_length))+1 seeks to find a row. For a table with 500,000 rows indexing a medium int: log(500,000)/log(1024/3*2/(3+4)) +1 = 4 seeks The above index would require: 500,000 * 7 * 3/2 = 5.2M. In real life, most of the blocks will be buffered, so probably only 1-2 seeks are needed.
• For writes you will need (as above) 4 seek requests, however, to find where to place the new key, and normally 2 seeks to update the index and write the row.
  For REALLY big databases, your application will be bound by the speed of your disk seeks, which increase by N log N as you get more data.
  Split databases and tables over different disks. In MySQL you can use symbolic links for this.
• Striping disks (RAID 0) will increase both read and write throughput.
  Striping with mirroring (RAID 0+1) will give you safety and increase the read speed. Write speed will be slightly lower.
• Don't use mirroring or RAID (except RAID 0) on the disk for temporary files or for data that can be easily re-generated..
• On Linux use hdparm -m16 -d1 on the disks on boot to enable reading/writing of multiple sectors at a time, and DMA. This may increase the response time by 5-50 %.
  On Linux, mount the disks with async (default) and noatime.
• For some specific application, one may want to have a ram disk for some very specific tables, but normally this is not needed.

Optimizing OS

• No swap; If you have memory problems, add more RAM instead or configure your system to use less memory.
• Don't use NFS disks for data (you will have problems with NFS locking).
• Increase number of open files for system and for the SQL server. (add ulimit -n # in the safe_mysqld script).
• Increase the number of processes and threads for the system.
  If you have relatively few big tables, tell your file system to not break up the file on different cylinders (Solaris).
• Use file systems that support big files (Solaris).
• Choose which file system to use; Reiserfs on Linux is very fast for open, read and write. File checks take just a couple of seconds.

Choosing API

PERL

• Portable programs between OS and databases
• Good for quick prototyping
• One should use the DBI/DBD interface

PHP

• Simpler to learn than PERL.
• Uses less resources than PERL, which makes it good for embedding in Web servers.
• One can get more speed by upgrading to PHP4.
  

C

• The native interface to MySQL.
• Faster and gives more control
• Lower level, so you have to work more.

C++

• Higher level gives you more time to code your application.
• Is still in development.
  

ODBC

• Works on Windows and Unix
• Almost portable between different SQL servers.
• Slow; MyODBC, which is a simple pass-through driver is 19 % slower than using a native interface.
• Many ways to do the same thing; Hard to get things to work as many ODBC drivers have different bugs in different areas.
• Problematic; Microsoft changes the interface once in a while.
• Insecure future (Microsoft pushes more for OLE than for ODBC).
  

JDBC

• In theory portable between OS and databases.
• Can be run in the web client.
• Python + others
• May be fine, but we don't use them.
• Optimizing the application
• One should concentrate on solving the problem.
• When writing the application one should decide what is most important:
  Speed
  Portability between OS
  Portability between SQL servers
  Use persistent connections.
  Cache things in your application to lessen the load of the SQL server.
• Don't query columns that you don't need in your application.
• Don't use SELECT * FROM table_name...
• Benchmark all parts of your application, but put the most effort into benchmarking the whole application under the worst possible 'reasonable' load. By doing this in a modular fashion you should be able to replace the found bottleneck with a fast 'dummy module', you can then easily identify the next bottleneck (and so on).
• Use LOCK TABLES if you do a lot of changes in a batch; For example group multiple UPDATES or DELETES together.
  

Portable applications should use
Perl DBI/DBD
ODBC
JDBC
Python (or any other language that has a generalized SQL interface)
You should only use SQL constructs which exist in all the target SQL servers or can easily be emulated with other constructs. The crash-me pages on www.mysql.com can help you with this.
Write wrappers to provide missing functionality for other OSes / SQL servers.
If you need more speed, you should:
Find the bottleneck (CPU, disk, memory, SQL server, OS, API, or application) and concentrate on solving this.
Use extensions that give you more speed / flexibility.
Get to know your SQL server so that you can use the fastest possible SQL constructs for your problem and avoid bottlenecks.
Optimize your table layouts and queries.
Use replication to get more select speed.
If you have a slow net connection to the database, use the compressed client/server protocol. Don't be afraid to make the first version of your application not perfectly portable; when you have solved your problem, you can always optimize it later.

Optimizing MySQL

• Choose compiler and compiler options.
• Find the best MySQL startup options for your system.
• Scan the the MySQL manual and read Paul DuBois' MySQL book.
• Use EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS and SHOW PROCESSLIST.
• Learn how the query optimizer works.
• Optimize your table formats.
• Maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE).
• Use MySQL extensions to get things done faster.
• Write a MySQL UDF function if you notice that you would need some function in many places.
• Don't use GRANT on table level or column level if you don't really need it.
• Pay for MySQL support and get help to solve your problem :)
  

Compiling and installing MySQL

By choosing the best possible compiler for your system, you can usually get 10-30 % better performance.
On Linux/Intel, compile MySQL with pgcc. (The Pentium optimized version of gcc). The binary will only work with Intel Pentium CPUs, however.
Use the optimize options that are recommended in the MySQL manual for a particular platform.
Normally a native compiler for a specific CPU (like Sun Workshop for Sparc) should give better performance than gcc, but this is not always the case.
Compile MySQL with only the character sets you are going to use.
Compile the mysqld executable statically (with --with-mysqld-ldflags=-all-static) and strip the final executable with strip sql/mysqld.
Note that as MySQL doesn't use C++ exceptions, compiling MySQL without exceptions support will give a big performance win!
Use native threads (instead of the included mit-pthreads) if your OS supports native threads.
Test the resulting binary with the MySQL benchmark test.
Maintenance
If possible, run OPTIMIZE table once in a while. This is especially important on variable size rows that are updated a lot.
Update the key distribution statistics in your tables once in a while with myisamchk -a; Remember to take down MySQL before doing this!
If you get fragmented files, it may be worth it to copy all files to another disk, clear the old disk and copy the files back.
If you have problems, check your tables with myisamchk or CHECK table.
Monitor MySQL status with: mysqladmin -i10 processlist extended-status
With the MySQL GUI client you can monitor the process list and the status in different windows.
Use mysqladmin debug to get information about locks and performance.
Optimizing SQLUse SQL for the things it's good at, and do other things in your application. Use the SQL server to:
Find rows based on WHERE clause.
JOIN tables
GROUP BY
ORDER BY
DISTINCT Don't use an SQL server:
To validate data (like date)
As a calculator Tips
Use keys wisely.
Keys are good for searches, but bad for inserts / updates of key columns.
Keep by data in the 3rd normal database form, but don't be afraid of duplicating information or creating summary tables if you need more speed.
Instead of doing a lot of GROUP BYs on a big table, create summary tables of the big table and query this instead.
UPDATE table set count=count+1 where key_column=constant is very fast!
For log tables, it's probably better to generate summary tables from them once in a while than try to keep the summary tables live.
Take advantage of default values on INSERT.

Important MySQL startup options

back_log
Change if you do a lot of new connections.
thread_cache_size
Change if you do a lot of new connections.
key_buffer_size
Pool for index pages; Can be made very big
bdb_cache_size
Record and key cache used by BDB tables.
table_cache
Change if you have many tables or simultaneous connections
delay_key_write
Set if you need to buffer all key writes
log_slow_queries
Find queries that takes a lot of time
max_heap_table_size
Used with GROUP BY
sort_buffer
Used with ORDER BY and GROUP BY
myisam_sort_buffer_size
Used with REPAIR TABLE
join_buffer_size
When doing a join without keys
Optimizing tables
MySQL has a rich set of different types. You should try to use the most efficient type for each column.

• The ANALYSE procedure can help you find the optimal types for a table: SELECT * FROM table_name PROCEDURE ANALYSE()
• Use NOT NULL for columns which will not store null values. This is particularly important for columns which you index.
• Change your ISAM tables to MyISAM.
• If possible, create your tables with a fixed table format.
• Don't create indexes you are not going to use.
• Use the fact that MySQL can search on a prefix of an index; If you have and INDEX (a,b), you don't need an index on (a).
• Instead of creating an index on long CHAR/VARCHAR column, index just a prefix of the column to save space. CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
• Use the most efficient table type for each table.
• Columns with identical information in different tables should be declared identically and have identical names.
  

How MySQL stores data

• Databases are stored as directories.
• Tables are stored as files.
• Columns are stored in the files in dynamic length or fixed size format. In BDB tables the data is stored in pages.
• Memory-based tables are supported.
• Databases and tables can be symbolically linked from different disks.
  
• On Windows MySQL supports internal symbolic links to databases with .sym files.
  

MySQL table types

• HEAP tables; Fixed row size tables that are only stored in memory and indexed with a HASH index.
• ISAM tables; The old B-tree table format in MySQL 3.22.
• MyISAM tables; New version of the ISAM tables with a lot of extensions:
  Binary portability.
  Index on NULL columns.
  Less fragmentation for dynamic-size rows than ISAM tables.
  Support for big files.
  Better index compression.
  Better key statistics.
  Better and faster auto_increment handling.
• Berkeley DB (BDB) tables from Sleepycat: Transaction-safe (with BEGIN WORK / COMMIT ROLLBACK).
  

MySQL row types (only relevant for ISAM/MyISAM tables)
MySQL will create the table in fixed size table format if all columns are of fixed length format (no VARCHAR, BLOB or TEXT columns). If not, the table is created in dynamic-size format.

Fixed-size format is much faster and more secure than the dynamic format.
The dynamic-size row format normally takes up less space but may be fragmented over time if the table is updated a lot.
In some cases it's worth it to move all VARCHAR, BLOB and TEXT columns to another table just to get more speed on the main table.

With myisampack (pack_isam for ISAM) one can create a read-only, packed table. This minimizes disk usage which is very nice when using slow disks. The packed tables are perfect to use on log tables which one will not update anymore.
MySQL caches (shared between all threads, allocated once)
Key cache ; key_buffer_size, default 8M
Table cache ; table_cache, default 64
Thread cache ; thread_cache_size, default 0.
Hostname cache ; Changeable at compile time, default 128.
Memory mapped tables ; Currently only used for compressed tables. Note that MySQL doesn't have a row cache, but lets the OS handle this!
MySQL buffer variables (not shared, allocated on demand)
sort_buffer ; ORDER BY / GROUP BY
record_buffer ; Scanning tables
join_buffer_size ; Joining without keys
myisam_sort_buffer_size ; REPAIR TABLE
net_buffer_length ; For reading the SQL statement and buffering the result.
tmp_table_size ; HEAP-table-size for temporary results.

How the MySQL table cache works

Each open instance of a MyISAM table uses an index file and a data file. If a table is used by two threads or used twice in the same query, MyISAM will share the index file but will open another instance of the data file.
The cache will temporarily grow larger than the table cache size if all tables in the cache are in use. If this happens, the next table that is released will be closed.
You can check if your table cache is too small by checking the mysqld variable Opened_tables. If this value is high you should increase your table cache!
MySQL extensions / optimization that gives you speed
Use the optimal table type (HEAP, MyISAM, or BDB tables).
Use optimal columns for your data.
Use fixed row size if possible.
Use the different lock types (SELECT HIGH_PRIORITY, INSERT LOW_PRIORITY)
Auto_increment
REPLACE (REPLACE INTO table_name VALUES (...))
INSERT DELAYED
LOAD DATA INFILE / LOAD_FILE()
Use multi-row INSERT to insert many rows at a time.
SELECT INTO OUTFILE
LEFT JOIN, STRAIGHT JOIN
LEFT JOIN combined with IS NULL
ORDER BY can use keys in some cases.
If you only query columns that are in one index, only the index tree will be used to resolve the query.
Joins are normally faster than subselects (this is true for most SQL servers).
LIMIT
SELECT * from table1 WHERE a > 10 LIMIT 10,20
DELETE * from table1 WHERE a > 10 LIMIT 10
foo IN (list of constants) is very optimized.
GET_LOCK()/RELEASE_LOCK()
LOCK TABLES
INSERT and SELECT can run concurrently.
UDF functions that can be loaded into a running server.
Compressed read-only tables.
CREATE TEMPORARY TABLE
CREATE TABLE .. SELECT
MyISAM tables with RAID option to split a file over many files to get over the 2G limit on some file system.
Delayed_keys
Replication

When MySQL uses indexes

Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.
SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
SELECT * FROM table_name WHERE key_part1 IS NULL;
When you use a LIKE that doesn't start with a wildcard.
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
Retrieving rows from other tables when performing joins.
SELECT * from t1,t2 where t1.col=t2.key_part
Find the MAX() or MIN() value for a specific index.
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
ORDER BY or GROUP BY on a prefix of a key.
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
When all columns used in the query are part of one key.
SELECT key_part3 FROM table_name WHERE key_part1=1
When MySQL doesn't use an index
Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table. For example if key_part1 is evenly distributed between 1 and 100, it's not good to use an index in the following query:
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
If you are using HEAP tables and you don't search on all key parts with =
When you use ORDER BY on a HEAP table
If you are not using the first key part
SELECT * FROM table_name WHERE key_part2=1
If you are using LIKE that starts with a wildcard
SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
When you search on one index and do an ORDER BY on another
SELECT * from table_name WHERE key_part1 = # ORDER BY key2
Learn to use EXPLAINUse EXPLAIN on every query that you think is too slow! mysql> explain select t3.DateOfAction, t1.TransactionID
-> from t1 join t2 join t3
-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> order by t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
table type possible_keys key key_len ref rows Extra
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
t1 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort
t2 ref ID ID 4 t1.TransactionID 13
t3 eq_ref PRIMARY PRIMARY 4 t2.GroupID 1
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
Types ALL and range signal a potential problem.
Learn to use SHOW PROCESSLISTUse SHOW processlist to find out what is going on: +----+-------+-----------+----+---------+------+--------------+-------------------------------------+
Id User Host db Command Time State Info
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
6 monty localhost bp Query 15 Sending data select * from station,station as s1
8 monty localhost Query 0 show processlist
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+

Use KILL in mysql or mysqladmin to kill off runaway threads.

How to find out how MySQL solves a query

Run the following commands and try to understand the output:
SHOW VARIABLES;
SHOW COLUMNS FROM ...\G
EXPLAIN SELECT ...\G
FLUSH STATUS;
SELECT ...;
SHOW STATUS;

MySQL is extremely good

• For logging.
• When you do many connects; connect is very fast.
• Where you use SELECT and INSERT at the same time.
• When you don't combine updates with selects that take a long time.
• When most selects/updates are using unique keys.
• When you use many tables without long conflicting locks.
• When you have big tables (MySQL uses a very compact table format).
• Things to avoid with MySQL
• Updates to a table or INSERT on a table with deleted rows, combined with SELECTS that take a long time.
• HAVING on things you can have in a WHERE clause.
• JOINS without using keys or keys which are not unique enough.
• JOINS on columns that have different column types.
• Using HEAP tables when not using a full key match with =
• Forgetting a WHERE clause with UPDATE or DELETE in the MySQL monitor. If you tend to do this, use the --i-am-a-dummy option to the mysq client.
  

Different locks in MySQL

Internal table locks.
LOCK TABLES (Works on all table types)
GET_LOCK()/RELEASE_LOCK()
Page locks (for BDB tables)
ALTER TABLE also does a table lock on BDB tables.
LOCK TABLES gives you multiple readers on a table or one writer.

Normally a WRITE lock has higher priority than a READ lock to avoid starving the writers. For writers that are not important one can use the LOW_PRIORITY keyword to let the lock handler prefer readers. UPDATE LOW_PRIORITY SET value=10 WHERE id=10;

Tricks to give MySQL more information to solve things betterNote that you can always comment out a MySQL feature to make the query portable: SELECT /*! SQL_BUFFER_RESULTS */ ...
SELECT SQL_BUFFER_RESULTS ...Will force MySQL to make a temporary result set. As soon as the temporary set is done, all locks on the tables are released. This can help when you get a problem with table locks or when it takes a long time to transfer the result to the client.
SELECT SQL_SMALL_RESULT ... GROUP BY ...To tell the optimizer that the result set will only contain a few rows.
SELECT SQL_BIG_RESULT ... GROUP BY ...To tell the optimizer that the result set will contain many rows.
SELECT STRAIGHT_JOIN ...Forces the optimizer to join the tables in the order in which they are listed in the FROM clause.
SELECT ... FROM table_name [USE INDEX (index_list) IGNORE INDEX (index_list)] table_name2Forces MySQL to use/ignore the listed indexes.

Example of doing transactions

How to do a transaction with MyISAM tables: mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;
How to do a transaction with Berkeley DB tables: mysql> BEGIN WORK;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> COMMIT;

Note that you can often avoid transactions altogether by doing: UPDATE customer SET value=value+new_value WHERE customer_id=some_id;
Example of using REPLACEREPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. Instead of using SELECT 1 FROM t1 WHERE key=#
IF found-row
LOCK TABLES t1
DELETE FROM t1 WHERE key1=#
INSERT INTO t1 VALUES (...)
UNLOCK TABLES t1;
ENDIF
Do REPLACE INTO t1 VALUES (...)

General tips

• Use short primary keys. Use numbers, not strings, when joining tables.
• When using multi-part keys, the first part should be the most-used key.
• When in doubt, use columns with more duplicates first to get better key compression.
• If you run the client and MySQL server on the same machine, use sockets instead of TCP/IP when connecting to MySQL (this can give you up to a 7.5 % improvement). You can do this by specifying no hostname or localhost when connecting to the MySQL server.
• Use --skip-locking (default on some OSes) if possible. This will turn off external locking and will give better performance.
• Use application-level hashed values instead of using long keys: SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
  col_1='constant' AND col_2='constant'
• Store BLOB's that you need to access as files in files. Store only the file name in the database.
• It is faster to remove all rows than to remove a large part of the rows.
• If SQL is not fast enough, take a look at the lower level interfaces to access the data.
  

Benefits of using MySQL 3.23

MyISAM ; Portable BIG table format
HEAP ; In memory tables

Berkeley DB ; Transactional tables from Sleepycat.

A lot of raised limits
Dynamic character sets
More STATUS variables.
CHECK and REPAIR table.
Faster GROUP BY and DISTINCT
LEFT JOIN ... IF NULL optimization.
CREATE TABLE ... SELECT
CREATE TEMPORARY table_name (...)
Automatic conversion of temporary HEAP to MyISAM tables
Replication
mysqlhotcopy script.
Important features that we are actively working on
Improving transactions
Fail safe replication
Text searching
Delete with many tables (Updates with many tables will be done after this.)
Better key cache
Atomic RENAME (RENAME TABLE foo as foo_old, foo_new as foo)
A query cache
MERGE TABLES
A better GUI client

This is a copy of MySQL tutorial provided by MySQL itself.

Reproduced here for any MySQL Database Developer, Programmers help.

We hope you will find these tips very useful and speed up your MySQL installations and Websites.

Regards

BollywoodSARGAM Database Development Team

http://www.BollywoodSARGAM.com

Optimising Tips for MySQL Databases running on large systems

Optimizing OS
* No swap; If you have memory problems, add more RAM instead or configure your system to use less memory.
* Don't use NFS disks for data (you will have problems with NFS locking).
* Increase number of open files for system and for the SQL server. (add ulimit -n # in the safe_mysqld script).
* Increase the number of processes and threads for the system.
* If you have relatively few big tables, tell your file system to not break up the file on different cylinders (Solaris).
* Use file systems that support big files (Solaris).
* Choose which file system to use; Reiserfs on Linux is very fast for open, read and write. File checks take just a couple of seconds.



Optimizing disks

Have one dedicated disk for the system, programs and for temporary files. If you do very many changes, put the update logs and transactions logs on dedicated disks.
Low seek time is important for the database disk; For big tables you can estimate that you will need: log(row_count) / log(index_block_length/3*2/(key_length + data_ptr_length))+1 seeks to find a row. For a table with 500,000 rows indexing a medium int: log(500,000)/log(1024/3*2/(3+4)) +1 = 4 seeks The above index would require: 500,000 * 7 * 3/2 = 5.2M. In real life, most of the blocks will be buffered, so probably only 1-2 seeks are needed.
For writes you will need (as above) 4 seek requests, however, to find where to place the new key, and normally 2 seeks to update the index and write the row.
For REALLY big databases, your application will be bound by the speed of your disk seeks, which increase by N log N as you get more data.
Split databases and tables over different disks. In MySQL you can use symbolic links for this.
Striping disks (RAID 0) will increase both read and write throughput.
Striping with mirroring (RAID 0+1) will give you safety and increase the read speed. Write speed will be slightly lower.
Don't use mirroring or RAID (except RAID 0) on the disk for temporary files or for data that can be easily re-generated..
On Linux use hdparm -m16 -d1 on the disks on boot to enable reading/writing of multiple sectors at a time, and DMA. This may increase the response time by 5-50 %.
On Linux, mount the disks with async (default) and noatime.
For some specific application, one may want to have a ram disk for some very specific tables, but normally this is not needed.

Choosing API
PERL
Portable programs between OS and databases
Good for quick prototyping
One should use the DBI/DBD interface
PHP
Simpler to learn than PERL.
Uses less resources than PERL, which makes it good for embedding in Web servers.
One can get more speed by upgrading to PHP4.
C
The native interface to MySQL.
Faster and gives more control
Lower level, so you have to work more.
C++
Higher level gives you more time to code your application.
Is still in development.
ODBC
Works on Windows and Unix
Almost portable between different SQL servers.
Slow; MyODBC, which is a simple pass-through driver is 19 % slower than using a native interface.
Many ways to do the same thing; Hard to get things to work as many ODBC drivers have different bugs in different areas.
Problematic; Microsoft changes the interface once in a while.
Insecure future (Microsoft pushes more for OLE than for ODBC).
JDBC
In theory portable between OS and databases.
Can be run in the web client.
Python + others
May be fine, but we don't use them.

For a more exhaustive list see next post

Regards
BollywoodSARGAM Database Developer Team

Tips on MySQL Database Optimization

* Use MySQL Commands like EXPLAIN, EXPLAIN EXTENDED and ANALYZE often to understand queries you are running
* Create indexes on fields you feel are most likely to be used in your queries.
* Design your tables with emphasis on proper data type. Do not use Data type like TEXT for fields like title of posts.
* Use proper table field names. A column named "t" will not make much sense to other programmers working with you, use fields like "post_title" so that one gets idea of what the column is for.
* Use mysql_pconnect and mysql_connect with intelligence. Do not blindly use mysql_pconnect just because it keeps a connection open for multiple uses. It can kill your server.

MySQL EXPLAIN Syntax : http://dev.mysql.com/doc/refman/5.0/en/explain.html

MySQL ANALYZE Syntax : http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html

That is it for now.

Regards

BollywoodSARGAM Database Development Team

Below is a Public Notice copy of the STATEMENT CONCERNING THE PUBLICATION BY PAY TELEVISION LICENSEES OF AN INDUSTRY CODE FOR PAY TELEVISION PROGRAMMING STANDARDS AND PRACTICES. These standards do not apply to BollywoodSARGAM website content as it is not a television company. However this document is helpful in understanding about making audience aware of the content they are about to see. This will help BollywoodSARGAM develop a system to alert users when viewing external embedded video clips on BollywoodSARGAM website. BollywoodSARGAM do not host or transmit any adult content or videos. BollywoodSARGAM is content aggregators and shows content from various websites like Youtube, Flicker and others.

---

Public Notice
Ottawa, 29 February 1984
Public Notice CRTC 1984-46
STATEMENT CONCERNING THE PUBLICATION BY PAY TELEVISION LICENSEES OF AN INDUSTRY CODE FOR PAY TELEVISION PROGRAMMING STANDARDS AND PRACTICES
In Public Notice CRTC 1983-16, issued on 26 January 1983 related to pay television programming, the Commission drew the attention of pay television licensees to their responsibilities under the Broadcasting Act for the programming distributed on their broadcasting undertakings. The Commission specifically stated:
While it accepts the idea that a discretionary service like pay television, which is not freely available to all viewers, can carry a wide range of programs, including adult programming, to appeal to a variety of interests and tastes, this does not detract from the duty of licensees to ensure that any such programming is presented in good taste, at the appropriate time, and is of high quality.
As to the question of the respective responsibilities of the Commission and the broadcasters, it must be stated clearly that:
1) the Commission and broadcasters must be guided by Section 3(c) of the Broadcasting Act;
2) the Commission is not a censorship body;
3) the Commission has no mandate to act pre-emptively with respect to events that have not yet occurred and with respect to programs that have not yet been aired;
4) it is for the courts to define key terms like obscenity.
Furthermore, the Commission undertook, in that Notice, to review its proposed pay television regulations taking into account the concerns expressed by the Task Force on Sex-Role Stereotyping and by the public at large relating to sexual exploitation and the portrayal of gratuitous violence against women. This concern was reflected in more than 8,000 complaints received by the Commission and the Minister of Communications from members of the public and from a variety of organizations and groups concerned with the scheduling and nature of some of the programming on pay television.
In response to such concerns, the Commission announced its intention in Public Notice CRTC 1983-16 to:
convene a meeting of pay television network licensees with a view to developing voluntary industry standards regarding their proposed adult programming. Among the matters the Commission intends to consider in this context are: the time of exhibition, the avoidance of the portrayal of sexual exploitation or sexual violence, and due concern for taste and quality in programming.
Subsequently, in Public Notice CRTC 1984-3, issued on 5 January 1984, the Commission revised its proposed pay television regulations, requiring pay television licensees to provide adequate information to subscribers with respect to the nature and audience suitability of any mature programming distributed on its undertaking. The Commission also announced it was considering the "inclusion of a prohibition against the distribution of any program containing comments or pictorial representations which, when taken in context, would be abusive of any race, religion or creed, or of either sex." As required by the Statutory Instruments Act, the proposed regulations were forwarded to the Clerk of the Privy Council for review. This review is still in progress.
Pursuant to Public Notice CRTC 1983-16, a number of meetings and discussions between pay television licensees and Commission staff were held in 1983 and early 1984 concerning the licensees' plans for developing voluntary industry standards regarding pay television programming.
On 15 February 1984, the Commission received a document entitled Pay Television Programming Standards and Practices , a copy of which is attached to this Notice as Appendix I. The code has been endorsed by the following pay television licensees:
- Star Channel Services Limited; - Ontario Independent Pay Television Limited; - Allarcom Limited; - Aim Satellite Broadcasting Corporation; - First Choice Canadian Communications Corporation.
With respect to Premier Choix: TVEC, the Commission has been advised that it intends to file a French-language document on behalf of Premier Choix: TVEC as soon as possible, which will also incorporate the principles set forth (in the attached document).
At this time, the Commission wishes to acknowledge receipt of the code developed by the pay television industry and invites all interested parties to submit their comments directly to the pay television licensees by writing to them at the addresses listed in Appendix II of this Notice.
The Commission will require pay television licensees to submit, within 60 days of the date of this Notice, a full report on the comments and concerns received in regard to the voluntary code and the licensee's response, and it will thereafter determine what further action, if any, may be required.
J.G. Patenaude Secretary General
APPENDIX I February 15, 1984
PAY TELEVISION PROGRAMMING
STANDARDS AND PRACTICES
A. Introduction
Pay television network licensees in Canada are committed to the presentation of programming services which are well balanced, of high quality, and of interest to a wide number of Canadians. The programming so presented is intended to appeal to a variety of interests and tastes.
A major appeal of the premium pay television services in Canada as well as in the United States is the ability to see feature films and other programming material in their original theatrical form, uninterrupted by commercials.
Pay television is distinguished from conventional television as it requires an affirmative decision by a subscriber to receive it "unscrambled" in the home. As a discretionary service, pay television has more latitude to program material that is intended for mature audiences than is the case with conventional television.
Therefore, pay television network licensees have a responsibility to ensure that the programming they provide is of high quality and meets general community standards within the context of a discretionary Service.
B. Selection of Programs
1. Responsibility for Selection. As provided in the Broadcasting Act and in the conditions of licence, selection of programs is the responsibility of the particular pay television licensee. The network licensee is by law responsible for what is distributed and will not delegate this responsibility.
2. Relationship with Producers. In the course of approving the production, particularly prior to commencement of filming or taping, or in approving any changes during production, pay television licensees can influence producers positively in their exercise of good judgment and taste. In order to raise issues of concern with independent producers, pay television network licensees will distribute a copy of this document to all independent producers who apply for script and concept development funding, for pre-licensing of product, and to all regular program suppliers, whether Canadian or foreign.
3. Exercise of Discretion. The discretion in the selection of programs will be exercised by the programming personnel of the pay television network licensee, as directed by this policy statement, and by the management of the licensee. All material will be fully screened prior to airing.
4. Exercise of Discretion. The discretion of programming personnel will be exercised responsibly and in good taste. In particular, no material should be selected that is
(a) contrary to law, or
(b) offensive to general community standards.
"Community standards" will necessarily change over time and therefore will be subject to continuing review and evaluation. Pay television licensees will not select programming that would go beyond an "R-rating" or its equivalent, as established under Part C hereof.
5. Previews. Notwithstanding the above, where the program is aired in preview periods (i.e. when the programming is unscrambled and may be received whether or not the subscriber ordered it), pay television licensees will select programming that meets the same standards of scheduling and content that apply to conventional broadcasters.
C. Classification and Cautionary Warnings
1. Program Guide. In order that viewers will be able to exercise an informed choice on what they wish to watch on pay television, pay television licensees will provide a monthly program guide to their subscribers. They will also send out program information to all media for inclusion in their television listings. In addition to the single-letter classifications described below, pay television licensees will provide in their program guide where possible and appropriate, short descriptive warnings as to the nature of the material, e.g., "Adult situations and language", "graphic violence", "some nudity".
2. Single-Letter Classification. In order to provide broad guidance as to the suitability of the programming, pay television network licensees regularly provide at least the following classifications in their guides for each of their programs:
G Suitable for viewing by a general audience of all ages;
PG Parental guidance is advised;
R This material, if a feature film, was given a "Restricted" or "R-rating" in its theatrical release; or, if not released theatrically, is of equivalent suitability to R-rated films.
3. On-Air Warning. Where appropriate, pay television licensees will provide a cautionary warning on-the-air at the beginning of the program, indicating the information set out in Appendix A.
4. Decision on Classification. The decision as to classification will be made by the particular pay television licensee involved, based on screening the particular version intended to be aired. However, pay licensees will attempt to coordinate ratings of films so that the same warnings are used on all pay networks where material is duplicated. In making this decision, licensees should take into consideration any ratings or classifications that may have been given to the program by other appropriate industry or government bodies. In some cases, however, there may be no other ratings upon which a comparison can be made; in such cases, the pay television licensee will use its best judgment in assigning an equivalent rating.
D. Program Concerns
1. Sex-Role Stereotypinq. This question has been extensively explored in the Report of the Task Force on Sex-Role Stereotyping to the CRTC. While pay television networks depend on major studios as the primary source of their movie product, licensees have a responsibility to raise the issue with producers who seek script and concept development funding and prelicensinq of product. Pay television networks will seek to fund Programming that provides a balanced view of sex roles.
2. Gratuitous Violence Towards Women. The portrayal of gratuitous violence towards women will be avoided and pay television licensees will reflect this policy in their selection process described in there guidelines. Programming personnel will exercise particular care and discretion in prescreening this material and considering the context of any possibly objectionable material.
E. Scheduling of Programs
1. Scheduling. Pay television generally includes fewer programs per month than conventional broadcasting, but such programs are repeated more frequently to suit the convenience of the schedules of the subscribers. At the same time, pay television licensees are sensitive to the concerns expressed by some that mature material should not be scheduled in periods when school-age children are home. There may also be certain mature material that should only be programmed in late evening or early morning hours.
2. Family Viewing. Pay television licensees will exercise particular discretion in respect to the period from 4:00 p.m. to 8:00 p.m. on school days, and during the day on weekends or holidays, to program material suitable for family viewing.
3. Adult Movies or Programming. In addition, pay television licensees will exercise their discretion carefully in regard to programs of which sexually explicit and/or violent material is the dominant element, so that such programming will be scheduled in late evening or early morning hours only.
Appendix "A"
1. "Pay Licensee" is proud to present this program which is suitable for viewing by all ages.
2. The following program contains scenes of violence and therefore viewer discretion is advised.
3. The following program contains scenes which use coarse language. Viewer discretion is advised.
4. The following program contains scenes of nudity. Viewer discretion is advised.
5. The following program deals with mature subject matter. Viewer discretion is advised.
6. The following program contains scenes of extreme violence. Viewer discretion is advised.
7. The following program contains scenes of violence and coarse language. Viewer discretion is advised.
8. The following program contains scenes of nudity and violence. Viewer discretion is advised.
9. The following program deals with mature subject matter and contains scenes of violence and nudity. Viewer discretion is advised.
10. The following program contains scenes of nudity and coarse language. Viewer discretion is advised.
11. The following program deals with mature subject matter and contains scenes of nudity and coarse language. Viewer discretion is advised.
12. The following program contains scenes of extreme violence and coarse language. Viewer discretion is advised.
13. The following program contains scenes of explicit sexuality and nudity and may be offensive to some viewers. Therefore, this film is recommended for mature audiences only.
APPENDIX II
PAY TV LICENSEES
Fred Klinkhammer, President FIRST CHOICE CANADIAN COMMUNICATIONS CORPORATION 1 Dundas Street W., Suite 1700 Toronto, Ontario M5G 1Z3
Jocelyne Pelchat-Johnson Acting President PREMIER CHOIX: TVEC 666 Sherbrooke Street West, 15th Floor Montréal, Quebec H3A 1E7
Steven Harris, President ONTARIO INDEPENDENT PAY TELEVISION LIMITED 86 Bloor Street West, Suite 500 Toronto, Ontario M5S 1M5
Doug Holthy, President ALLARCOM LIMITED 5324 Calgary Trail, Suite 200 Edmonton, Alberta T6H 4J8
STAR CHANNEL SERVICES LIMITED 603-1 Sackville Place P.O. Box 1074 Halifax, Nova Scotia B3J 2X1
George Spracklin, President AIM SATELLITE BROADCASTING CORPORATION 108-900 West Georgia Street Vancouver, B.C. V6C 1P9