tips to write good sql queries

MySQL & MariaDB Other MySQL® Questions
1

Please provide the tips to write good sql queries as refer to performance wise

as I know some tips as follow

1:- in like statement avoid ‘%%’ instead use ‘%’ as possible
2:- appropriate use of INDEXES on select queries as for column where,group by
order by , having is required
3:- use explain to identify possible indexes
4:- avoid subqueries instead of these use inner join that is more efficient

NOTE:- indexes not always is helpful to increase performance

Please tell more tips as possible

2

  1. Like statements should not begin with ‘%’ if you want to take advantage of an index. So LIKE ‘%yahoo.com’ won’t ever use an index, but LIKE ‘bob@%’ will.

  2. Write readable SQL. Even for this simple example, which is easier to read?

select u.id,u.login,m.title,m.body,m.created_at,m.updated from users as u inner join messages as m on m.user-id = u.id where u.id = 1234

SELECT /* get a user’s messages for display */ u.id, u.login, m.title, m.body, m.created_at, m.updated_atFROM users AS uINNER JOIN messages AS mON m.user_id = u.idWHERE u.id = 1234