Optimize MySQL queries |
MySQL is powerfull enough to make almost anything in web development. Even wikipedia uses it. Of course, I am not guru to understand and make such a big system. But even most of small projects need optimisation. By optimising MySQL queries, you get faster responses from server, smaller database size etc. Size does matter when you use MySQL databases on shared hostings.
There is small article on MySQL optimisation in linuxformat.co.uk wiki
What I did learn from this article:
-
SELECT password FROM users WHERE Username = 'myuser' LIMIT 1;
Always select just fields which you will need in current situation, SELECT * ... is not good in most cases. If field by which you filter data, is unique, use LIMIT 1 statement.
- Create fields with as small data types as it is possible. Good example with age field. It is better to use TINYINT UNSIGNED than INT (who lives negative years and reaches 255 year old? )
- When database has lots of data, you can check is your schemata is optimised with query:
SELECT * FROM table PROCEDURE ANALYSE();
It will show you optimal field data type by analyzing records (look at Optimal_fieldtype column)
- Add Index to most searchable columns:
ALTER TABLE tablename ADD INDEX name(column)
MySQL Index is similar to book's index.