php mysql optimisation -

i optimize php scripts, i'm implementing memcached(it reduce time from: 30 secs 5 secs) php. first think must see script @ app.promls.net, takes 3 seconds on build it(view source @ end , you'll se comment box time execution).

next thinks optimize select statement using explain:

select sql_calc_found_rows p.id , p.type, p.bathrooms, p.bedrooms, p.for_sale, p.for_rent, p.rent_price, p.sale_price, p.min_price, p.mid_price, p.hig_price, p.units, p.negotiation, p.status, p.square_meters, p.commission, p.address, p.currency_type, p.creation_date, p.modified_date, p.parent, p.property_name, p.area_id, p.amenities, p.unit_number, p.levels, p.for_vacational, p.construction_year, p.construction_density, p.plot_meters, p.community_fees, p.garbage_tax, p.mortage, p.accompanied_visit, p.sale_sign, (select up.path uploads up.property_id = p.id order position asc,id asc limit 0,1) image, p.ref_catastral, p.vacational_term, p.property_keys, p.owner_id, p.property_type , pt.name_es category, ci.description city, ci.id city_id, es.description estate, es.parent estate_id, co.description country, co.parent country_id, u.id brokerid, u.fullname brokername, u.phone brokerphone, u.cellphone brokermobile , u.username brokeremail, c.address companyaddress, c.phone companyphone, c.name companyname, c.website companyweb, c.email companyemail, c.id companyid properties p inner join property_types pt on pt.id = p.property_type inner join areas ci on ci.id = p.area_id inner join areas es on es.id = ci.parent inner join areas co on co.id = es.parent inner join users u on u.id = p.created_by inner join company c on c.id = p.company_id p.status in('active','active-rented','active-sold') order p.min_price asc, p.mid_price asc, p.hig_price asc, p.rent_price asc, p.sale_price asc limit 0, 10 

the explain :

> 1, 'primary', 'p', 'all', > 'property_area,property_status', '', > '', '', 142, 'using where; using > temporary; using filesort' 1, > 'primary', 'c', 'ref', 'primary', > 'primary', '4', > 'inmobili.p.company_id', 1, 'using > where' 1, 'primary', 'pt', 'eq_ref', > 'primary', 'primary', '4', > 'inmobili.p.property_type', 1, 'using > where' 1, 'primary', 'u', 'all', > 'primary', '', '', '', 4, 'using > where' 1, 'primary', 'ci', 'eq_ref', > 'primary', 'primary', '4', > 'inmobili.p.area_id', 1, '' 1, > 'primary', 'es', 'eq_ref', 'primary', > 'primary', '4', 'inmobili.ci.parent', > 1, '' 1, 'primary', 'co', 'eq_ref', > 'primary', 'primary', '4', > 'inmobili.es.parent', 1, '' 2, > 'dependent subquery', 'up', 'ref', > 'property_uploads', > 'property_uploads', '4', > 'inmobili.p.id', 5, 'using where; > using filesort' 

as can see returns me 142 rows table properties , temporary table, thats because has next condition:

where p.status in('active','active-sold','active-rented'); 

so fix implement index on p.status. mismaching when use 1 value as:

where p.status in('active'); 

it returns me 77 rows , in extra: "using where; using temporary; using filesort" on explain; if change condition, if take off order condition makes same 77 rows , in extra: "using where; " know how optimize mysql using explain , when selection contains "order" or "group by" statements.

by way have hear using store procedures more faster plain select php, loooking in source of wordpress dont use procedure assume use procedures not solution.

how ever thinking use hiphop php maybe help.

i'm using dedicated server. configuration php must fine, cause have sub domain loads faster app, script same db firebird , i'm implementing memcached , store procedures.

what think, need firebird or mysql manage this?

i appreciate help, if see app takes 30 seconds execute script need optimize it;

tell me if need more information, i'll absolutely available gave it! lets chat msn or skype if wish.

thanks in advance

tell me if need more information

first think should provide schema(domain). think should provide image using tool example sql2diagram(googled this). maybe should first optimize schema

actually i'm implementing memcached(it reduce time from: 30 secs 5 secs) php

first know how can take long data memcached(memory). should not take time @ data memcached. wondering if need invalidate cache lot or if 1 time query. because in case need mysql put new data in cache. if possible think should offline in advance using message queue example redis(i use redis datastore) or beanstalkd. going make site fast again.

how ever thinking use hiphop php maybe help.

it can reduce cpu usage can safe money. if query bad don't think hiphop cut it

do need firebird or mysql manage this?

mysql should able manage it, find query complicated. think doing joins(you should reformat query because hard read). should optimize saying. in past needed database normalization. now think should use more memory, disc space instead avoid joining(expensive!!) tables. after should construct sql calcutation in set , predicate notation. paper got university, found pain in ass in past, did results it.

but honest have not used sql in long time, because hooked redis lightning fast. think lot easier implement on top of redis.