Optimizing MySql query

Question

I would like to know if there is a way to optimize this query :

SELECT
    jdc_organizations_activities.*,
    jdc_organizations.orgName, 
    CONCAT(jos_hpj_users.firstName, ' ', jos_hpj_users.lastName) AS nameContact  
FROM jdc_organizations_activities
LEFT JOIN jdc_organizations ON jdc_organizations_activities.organizationId =jdc_organizations.id
LEFT JOIN jos_hpj_users ON jdc_organizations_activities.contact = jos_hpj_users.userId
WHERE jdc_organizations_activities.status LIKE 'proposed'  
ORDER BY jdc_organizations_activities.creationDate DESC LIMIT 0 , 100 ;

Now When i see the query log :

 Query_time: 2  
 Lock_time: 0  
 Rows_sent: 100  
 Rows_examined: **1028330**

Query Profile :

2) Should i put indexes on the tables having in mind that there will be a lot of inserts and updates on those tables .

From Tizag Tutorials :

Indexes are something extra that you can enable on your MySQL tables to increase performance,cbut they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

Update after adding indexes and removing the lower() , group by and the wildcard

Time: 0.855ms

Answer 1

Add indexes (if you haven't) at:

Table: jdc_organizations_activities

• simple index on creationDate
• simple index on status
• simple index on organizationId
• simple index on contact

And rewrite the query by removing call to function LOWER() and using = or LIKE. It depends on the collation you have defined for this table but if it's a case insensitive one (like latin1), it will still show same results. Details can be found at MySQL docs: case-sensitivity

SELECT a.*
     , o.orgName
     , CONCAT(u.firstName,' ',u.lastName) AS nameContact  

FROM jdc_organizations_activities AS a
  LEFT JOIN jdc_organizations AS o
    ON a.organizationId = o.id 
  LEFT JOIN jos_hpj_users AS u
    ON a.contact = u.userId

WHERE a.status LIKE 'proposed'     --- or (a.status = 'proposed')

ORDER BY a.creationDate DESC 
LIMIT 0 , 100 ;

It would be nice if you posted the execution plan (as it is now) and after these changes.

---

UPDATE

A compound index on (status, creationDate) may be more appopriate (as Darhazer suggested) for this query, instead of the simple (status). But this is more guess work. Posting the plans (after running EXPLAIN query) would provide more info.

I also assumed that you already have (primary key) indexes on:

• jdc_organizations.id
• jos_hpj_users.userId