problem with query, or so says my host

[lucidtech]

I have a query that pulls data from a table based on the last date/time a user viewed a page, in order to see which of your 'friends' are online. I currently have about 3000 members, the user_friends table that is being queried has 46,000 records total. This is what my hosting company has told me, which makes no sense to me how they came up with such a HUGE number of rows being examined... any ideas?

From Host : 'We have disabled your database to return the server to normal usage. To re-enable your database, you will need to correct the following query:

-SELECT user_id2 AS online_id, user_last_call_date2 AS user_last_call_date, friend_top1 AS friend_top
FROM user_friends
WHERE user_id1 = 2286
AND user_last_call_date2 >= '{ts \'2010-12-13 07:22:37\'}'
UNION
SELECT user_id1 AS online_id, user_last_call_date1 AS user_last_call_date, friend_top2 AS friend_top
FROM user_friends
WHERE user_id2 = 2286
AND user_last_call_date1 >= '{ts \'2010-12-13 07:22:37\'}'
ORDER BY friend_top DESC, user_last_call_date DESC

EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY user_friends ALL 46240 Using where
2 UNION user_friends ALL 46240 Using where
UNION RESULT ALL Using filesort

This query examines 2138137600 rows, which is unacceptable in shared hosting.'

 

[r937]

put an index on user_id1, and another on user_id2

their record count is bullshït, you can see the two table scans of only 46240 rows right there in the EXPLAIN

what they did was multiply 46240*46240, which shows that they have no idea how a UNION works

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lucidtech]

Thank you. I figured that number was BS, my entire database doesn't have 2 billion records in it.

 

[intelwizrd]

I agree with r937, they don't know what they are talking about.

A question, Would an index on user_id1, user_last_call_date2 and another on user_id2, user_last_call_date1 be better for that query? Just a thought.

 

[r937]

Would an index on user_id1, user_last_call_date2 and another on user_id2, user_last_call_date1 be better for that query?

marginally

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lucidtech]

meaning create an index that includes both the 'user_id1' and 'user_last_call_date2' (in a single index) and another for the opposite?

 

[r937]

yes, compound indexes

ALTER TABLE user_friends
ADD INDEX ( user_id1, user_last_call_date2 );

ALTER TABLE user_friends
ADD INDEX ( user_id2, user_last_call_date1 );

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[intelwizrd]

Yes, would look this for the TSQL command, obviously change the index name and database name.

CREATE INDEX indexname1 ON [database].[dbo].[user_friends] ([user_id1],[user_last_call_date2])


CREATE INDEX indexname2 ON [database].[dbo].[user_friends] ([user_id2],[user_last_call_date1])

 

[intelwizrd]

guess, I am a little slow this morning

Need more coffee!

 

[r937]

intelwizrd, better make it a double

it's a mysql database, not sql server

(see the EXPLAIN output in post #1)



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon