MySQL/PostgreSQL – how to improve and find issues
Things to consider to improve your queries
- Do you need to retrieve all the columns for each row in the database by executing queries like (Select * from user), try to avoid this especially when you are showing many rows in a list (HTML table,…), so in that case choose the needed fields;
Notice: this can be different on Row vs Column Oriented Databases
- Do you retrieve all database table records to just get the actual count? well you can use count for example:
1SELECT COUNT(email) FROM users;
- Optimizing columns by reducing the size: do you need varchar(255) for a first_name, last_name, phone_number? then try to add an upper limit like varchar(80) depends on the data. In this way you will save some space.
- If you have a database table that will hold just country names why use datatype of unsigned int? use unsigned tiny int
- In MySQL old versions there was a limit for varchar to be at max 255, but in newer versions like MySQL 5.8 you are limited just by the row size, so it might be better to use varchar instead of text for 400 characters, Hint: text data is different, because it is stored in external storage
- Indexes – indexes can be very important to improve the performance, but use it for columns you actually use in your queries like filtering data by name, category
Find slow queries
- MySQL includes the EXPLAIN statement, which provides information about how MySQL executes queries. More information on this.
- There is a configuration options in mysqld.cnf file to configure options for the MySQL server. Find these options: (slow-query-log, long_query_time) They can help you find slow queries which exceeds a specific limit time (which you can also configure, for example two seconds) in log files. More information on this.
I hope this was helpful to you.
Will try to add other helpful things maybe in another post.