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

The slowness is because mysql has to apply lower() to every row. The solution is to create a new column to store the result of lower, then put an index on that column. Let's also use a trigger to make the solution more luxurious. OK, here we go:

a) Add a new column to hold the lower version of status (make this varchar as wide as status):

ALTER TABLE jdc_organizations_activities ADD COLUMN status_lower varchar(20);

b) Populate the new column:

UPDATE jdc_organizations_activities SET status_lower = lower(status);

c) Create an index on the new column

CREATE INDEX jdc_organizations_activities_status_lower_index
    ON jdc_organizations_activities(status_lower);

d) Define triggers to keep the new column value correct:

DELIMITER ~;
CREATE TRIGGER jdc_organizations_activities_status_insert_trig
BEFORE INSERT ON jdc_organizations_activities
FOR EACH ROW
BEGIN
    NEW.status_lower = lower(NEW.status);
END;

CREATE TRIGGER jdc_organizations_activities_status_update_trig
BEFORE UPDATE ON jdc_organizations_activities
FOR EACH ROW
BEGIN
    NEW.status_lower = lower(NEW.status);
END;~
DELIMITER ;

Your query should now fly.

Answer 2

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

Answer 3

Post the result from EXPLAIN Generally you need indexes on jdc_organizations_activities.organizationId, jdc_organizations_activities.contact, composite index on jdc_organizations_activities.status and jdc_organizations_activities.creationDate

Why you are using LIKE query for constant lookup (you have no wildcard symbols, or maybe you've edited the query) The index on status can be used for LIKE 'proposed%' but can't be used for LIKE '%proposed%' - in the later case better leave only index on creationDate

Answer 4

What indexes do you have on these tables? Specifically, have you indexed jdc_organizations_activities.creationDate?

Also, why do you need to group by jdc_organizations_activities.id? Isn't that unique per row, or can an organization have multiple contacts?