performance - Optimizing MySql query -

i know if there way optimize query :

select     jdc_organizations_activities.*,     jdc_organizations.orgname,      concat(jos_hpj_users.firstname, ' ', jos_hpj_users.lastname) namecontact   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 jdc_organizations_activities.status 'proposed'   order jdc_organizations_activities.creationdate desc limit 0 , 100 ; 

now when see query log :

 query_time: 2    lock_time: 0    rows_sent: 100    rows_examined: **1028330** 

query profile :

2) should put indexes on tables having in mind there lot of inserts , updates on tables .

from tizag tutorials :

indexes can enable on mysql tables increase performance,cbut have downsides. when create new index mysql builds separate block of information needs updated every time there changes made table. this means if updating, inserting , removing entries in table have negative impact on performance.

update after adding indexes , removing lower() , group , wildcard

time: 0.855ms

add indexes (if 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 query removing call function lower() , using = or like. depends on collation have defined table if it's case insensitive 1 (like latin1), still show same results. details can found @ mysql docs: case-sensitivity

select a.*      , o.orgname      , concat(u.firstname,' ',u.lastname) namecontact    jdc_organizations_activities   left join jdc_organizations o     on a.organizationid = o.id    left join jos_hpj_users u     on a.contact = u.userid  a.status 'proposed'     --- or (a.status = 'proposed')  order a.creationdate desc  limit 0 , 100 ; 

it nice if posted execution plan (as now) , after these changes.

---

update

a compound index on (status, creationdate) may more appopriate (as darhazer suggested) query, instead of simple (status). more guess work. posting plans (after running explain query) provide more info.

i assumed have (primary key) indexes on:

• jdc_organizations.id
• jos_hpj_users.userid