Have any idea to optimize the profile field query in D6?

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
chinabruce's picture
Posted by chinabruce on June 24, 2011 at 5:57am

Such as

SELECT f.name, f.type, v.value FROM profile_fields f INNER JOIN profile_values v ON f.fid = v.fid WHERE uid = xxxx;

lots of this query in my slow_query_log which the execute time > 2s.

Comments

explain

Posted by mikeytown2 on June 24, 2011 at 6:12am
mikeytown2's picture

what does EXPLAIN say about the query?

Profile vs. Content Profile

Posted by adixon on June 24, 2011 at 9:12pm
adixon's picture

You probably want to look at the urls that these are coming from and decide whether you really need this - sounds like you're pulling a whole lot of profile information for just one user.

Also - the profile module in core puts each profile value in a separate record, so one profile can pull many records. If you use the content profile module then your tables are set up differently and you only get one row per user, and you may get other kinds of caching optimization that cck nodes provide (i.e. cache_content).

In any case, try running that query for one of the users directly from mysql and it might give you more clues.

Re: optimization ideas

Posted by alexpavlovic on July 18, 2011 at 5:06am
alexpavlovic's picture

Hi,

Take a look at EXPLAIN first as mentioned above. See if any indexes can be added to speed up the search.
Next take a look at mysql server settings including query cache. Also you can read more here on tuning
mysql for Drupal: http://drupal.org/node/51263

Generally InnoDB is recommended as table engine.

Cheers.