I installed MySQL Tuner and this was the result of the report:
- Enable the slow query log to troubleshoot bad queries
- Adjust your join queries to always utilize indexes
So, i enabled the slow query log and it reported these kind of queries:
SELECT u.PIN,u.name,u.lastname,u.id_country,t1.id_user_list,COUNT(t2.id_user_list) AS q1 FROM contact AS t1, contact AS t2, user AS u WHERE t1.id_user=N AND t2.id_user = t1.id_user_list AND u.id_user = t1.id_user_list GROUP BY t1.id_user_list ORDER BY name,lastname
And...
SELECT * FROM user WHERE city LIKE 'S' AND gender = N AND flg_pic = N AND flg_hidden = N AND flg_ban = N AND id_user != N ORDER BY id_user DESC LIMIT N,N
I just need some help regarding the optimization of my tables, which are:
- contact
- id_contact (int / primary index)
- id_user (int / index)
- id_user_list (int / index)
- user
- id_user (int / primary index)
- name (varchar)
- lastname (varchar)
- PIN (varchar)
- id_country (int)
- city (varchar)
- gender (1 or 2)
- flg_pic (1 or 0)
- flg_hidden (1 or 0)
- flg_ban (1 or 0)
Should i index all varchars that are used in the queries (WHERE and ORDER) as well? Thanks for your help.
