3

I have a Drupal 6 website which is hosted on a shared hosting service and which is used to communicate with a desktop software application. Over the years I extended the user profile with over 70 different profile fields. With over 45.000 users this resulted in a profile_values table containing over 1.200.000 rows and I was getting complaints about slow queries and I've read it might lead to performance issues. Switching host or upgrading Drupal 6 to Drupal 7 is not an option!

Since the profile data is only used when the software communicates with the website, which is done once per application start I did not really have a need for such a large profile table so I created a custom module which stores the profile data per user in a single row as a serialized array. This also has the benefit that I don't need to update my website every time a new profile value is added in the software.

But my MySQL slow query log is filling up again. The query that's logged is SELECT * FROM {my_table}. As I know this table is quite large I was careful not do call such a query myself and therefore I cannot find this query in my module or any of the modules that use my module. I always select a single column and pass a specific parameter to check.

As far as I can see there are only two functions that might have an influence on this. I implement hook_user() for insert and update operations and I call drupal_write_record() when saving the user data. But running through the code I fail to see where such a query might be called.

Can anyone explain where this query might originate from, within the Drupal system, or how I can extend MySQL (5.1) logging to tell me from which script this function might originate? (As it's only supposed to be called from a software application there's no equivalent web page a user can access.)

4

If grep/ack-grep can't find a SELECT * (likely) then just add an $debug_query = 'SELECT * FROM {mytable}'; if (substr($query, 0, strlen($debug_query)) == $debug_query) error_log(print_r(debug_backtrace(), TRUE), 4) or match the whole query etc and then look at the Apache error log for the pouring in backtraces. If you dont want to trash the log, log into a file in /tmp.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.