tips to write good sql queries

  • Filter
  • Time
  • Show
Clear All
new posts

  • tips to write good sql queries

    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