Tips to optimize MySQL for better performance

I ave been working on my project www.edugoog.com and try to optimise queries. To do so I came through many articles. I gathered basic points from the same. Please find my finding below and feel free to add more in comments if missed.

1. Don’t query columns you don’t need, avoid using SELECT * FROM
2. Use caching to reduce database load
3. Normalize tables to ensure data consistency
4. Use persistent connections
5. Proper use of indexes improve performance
6. Do not perform calculations on an index (eg: if you have an index for a column called salary, do not perform calculation such as salary * 2 > 10000)
7. “LOAD DATA INFILE” is the fastest way to insert data into MySQL database (20 times faster than normal inserts)
8. Use INSERT LOW PRIORITY or INSERT DELAYED if you want to delay inserts from happening until the table is free
9. Use TRUNCATE TABLE rather than DELETE FROM if you are deleting an entire table (DELETE FROM delete row by row, whereas TRUNCATE TABLE deletes all at once)
10. Always use EXPLAIN to examine if your select query is inefficient
11. Use OPTIMIZE TABLE to reclaim unused space (Note: Table will be locked during optimisation, so only do it during low traffic time)
12. Better to have 10 quick queries than 1 slow one
13. MySQL can search on prefix of indexes (ie: If you have index INDEX (a,b), you don’t need an index on (a))
14. Don’t use HAVING when you can use WHERE
15. Use numeric values (rather than alphabetical values) when performing a join

Thanks

MySql - Manage users and user roles

I cam across to create a new user with restricted access for the database. Just to allow user to view, update and delete.
The below is just a brief step to do the task. To learn and understand , it would be better to look at mysql online manual


Step 1: Go to command propmt, and login to mysql

[root@sun ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3384
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
* make sure username has the rights to create and grant access to new users :)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE on {TABLENAME}.* to '{USERNAME}'@'{DOMAINNAME}';
Query OK, 0 rows affected (0.04 sec)


The above user is created with password as null.

the other alternative is to create a user first,

CREATE USER '{USERNAME}'@'{DOMAINNAME}' IDENTIFIED BY 

'{PASSWORD}';

and than

GRANT SELECT,INSERT,UPDATE,DELETE on {TABLENAME}.* to 

'{USERNAME}'@'{DOMAINNAME}'; 

Hope it helps someone.

Feel free to provide your suggestion.