UBB.threads PHP Forum Software Community Forums Resources and Customizing How Do I? mysql slow problems! HELP!

I just upgraded to 7-4-1, a few days later my hosting company suspended my site. I was informed by my host that the site has a mysql slow queries problem. I have to admit, I have no idea what this means?

My host says it's a problem with the ubb software--says my account will remain frozen until I have it optimized. ?? Below is just a sample of what in my mysql_slow_queries folder.

Help!

# Mon Dec 15 18:00:04 2008
# Query_time: 491 Lock_time: 240 Rows_sent: 1 Rows_examined: 1
use ascensio_gravsportsice07;
SELECT t2.USER_TIME_OFFSET,t2.USER_TOPIC_VIEW_TYPE,t2.USER_TIME_FORMAT, t1.USER_ID, t1.USER_DISPLAY_NAME, t1.USER_PASSWORD, t1.USER_SESSION_ID, t1.USER_MEMBERSHIP_LEVEL, t1.USER_IS_BANNED,t1.USER_RULES_ACCEPTED, t1.USER_IS_UNDERAGE, t2.USER_TOTAL_PM, t2.USER_STYLE, t2.USER_HIDE_LEFT_COLUMN,t2.USER_HIDE_RIGHT_COLUMN, t2.USER_LANGUAGE, t2.USER_MOOD, t2.USER_RELATIVE_TIME, t2.USER_TIME_OFFSET,t2.USER_SHOW_ALL_GRAEMLINS, t2.USER_AVATAR, t2.USER_TITLE, t2.USER_CUSTOM_TITLE, t2.USER_NAME_COLOR, t2.USER_SHOW_LEFT_MYSTUFF
FROM ubbt_USERS as t1,
ubbt_USER_PROFILE as t2
WHERE t1.USER_ID = 341
AND t1.USER_ID = t2.USER_ID

# Mon Dec 15 18:00:05 2008
# Query_time: 838 Lock_time: 582 Rows_sent: 0 Rows_examined: 0
use ascensio_gravsportsice07;
SELECT t2.USER_TOPIC_VIEW_TYPE, t2.USER_TIME_FORMAT, t2.USER_TOPICS_PER_PAGE, t1.USER_ID, t1.USER_DISPLAY_NAME, t1.USER_PASSWORD, t1.USER_SESSION_ID, t1.USER_MEMBERSHIP_LEVEL, t1.USER_IS_BANNED,t1.USER_RULES_ACCEPTED, t1.USER_IS_UNDERAGE, t2.USER_TOTAL_PM, t2.USER_STYLE, t2.USER_HIDE_LEFT_COLUMN,t2.USER_HIDE_RIGHT_COLUMN, t2.USER_LANGUAGE, t2.USER_MOOD, t2.USER_RELATIVE_TIME, t2.USER_TIME_OFFSET,t2.USER_SHOW_ALL_GRAEMLINS, t2.USER_AVATAR, t2.USER_TITLE, t2.USER_CUSTOM_TITLE, t2.USER_NAME_COLOR, t2.USER_SHOW_LEFT_MYSTUFF
FROM ubbt_USERS as t1,
ubbt_USER_PROFILE as t2
WHERE t1.USER_ID = 0
AND t1.USER_ID = t2.USER_ID

# Mon Dec 15 18:00:05 2008
# Query_time: 238 Lock_time: 218 Rows_sent: 0 Rows_examined: 0
use ascensio_gravsportsice07;
update ubbt_USERS
set USER_SESSION_ID = '381a4c3e139cd966cef9407ab2419a9a'
where USER_ID = 341

Those queries are simple select queries, there really is no reason for them to take that long at all. The USER_ID field is the primary key in those tables so they should be fast. I'd say there is a bit more going on than they are saying.

If you can at least get access to phpmyadmin, then what you can do is run that exact query and put an EXPLAIN at the front and then post it here. That will show if it's using the index or not. So what you'd do is run it like this:

Rick, here's what the query returned:
SQL query:

EXPLAIN SELECT t2.USER_TIME_OFFSET, t2.USER_TOPIC_VIEW_TYPE, t2.USER_TIME_FORMAT, t1.USER_ID, t1.USER_DISPLAY_NAME, t1.USER_PASSWORD, t1.USER_SESSION_ID, t1.USER_MEMBERSHIP_LEVEL, t1.USER_IS_BANNED, t1.USER_RULES_ACCEPTED, t1.USER_IS_UNDERAGE, t2.USER_TOTAL_PM, t2.USER_STYLE, t2.USER_HIDE_LEFT_COLUMN, t2.USER_HIDE_RIGHT_COLUMN, t2.USER_LANGUAGE, t2.USER_MOOD, t2.USER_RELATIVE_TIME, t2.USER_TIME_OFFSET, t2.USER_SHOW_ALL_GRAEMLINS, t2.USER_AVATAR, t2.USER_TITLE, t2.USER_CUSTOM_TITLE, t2.USER_NAME_COLOR, t2.USER_SHOW_LEFT_MYSTUFF
FROM ubbt_USERS AS t1, ubbt_USER_PROFILE AS t2
WHERE t1.USER_ID =341
AND t1.USER_ID = t2.USER_ID

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

????

Ok, so what you should do is send that to your host. If they are somewhat knowledgeable in MySQL then they should be able to read that ok and see that it's not a software issue with those queries taking so long.

Basically, it's saying that it's using the Primary key index on both tables and it's only having to examine 1 row to find the result. If the last number was large, then we'd be looking at an issue since that means it has to examine multiple rows to find the result.

There most be other things going on with that MySQL server contributing to the slowdowns. If you're on a shared server then that could be part of it.

On the phone with hosting company now...
Hopefully this is easily resolved.

Thanks you VERY much Rick!!

EJH