Page 1 of 1

Forever query

Posted: Thu Jan 31, 2008 4:37 am
by shiznatix
I have this query that runs quite quickly without a join, just on the one table. Now I added in a left join to try to get a bit more information but it is now taking like 20 seconds to run this 1 query which is nuts. Here is my query:

Code: Select all

 
SELECT
    `rt`.*,
    `um`.`id` AS `usermap_id`
FROM
        `rb_raketracking` AS `rt`
LEFT JOIN
        `rb_usermap` AS `um`
ON
        rt.room_username = um.room_username
        AND
        rt.fk_room_id = um.fk_room_id
WHERE
    (rt.fk_room_id = '4')
    AND (rt.data_date LIKE "2008-01%")
 
now if I remove the left join it runs in seconds. The table rb_usermap does not have a lot of info in there so I don't see why this is crushing the query so drastically. Any help is much appreciated.

Re: Forever query

Posted: Fri Feb 01, 2008 7:50 am
by shiznatix
*bump* anyone know why such a simple left join would cause the query to slow down 200x plus?

Re: Forever query

Posted: Fri Feb 01, 2008 8:32 am
by Inkyskin
Are the the columns rt.room_username, um.room_username, rt.fk_room_id, um.fk_room_id indexed?

Re: Forever query

Posted: Fri Feb 01, 2008 8:42 am
by shiznatix
Inkyskin wrote:Are the the columns rt.room_username, um.room_username, rt.fk_room_id, um.fk_room_id indexed?
Negative. What would that do?

Re: Forever query

Posted: Fri Feb 01, 2008 8:55 am
by John Cartwright
shiznatix wrote:
Inkyskin wrote:Are the the columns rt.room_username, um.room_username, rt.fk_room_id, um.fk_room_id indexed?
Negative. What would that do?

Likely make your query faster :)

Generally you should always index any columns that are joined.

Re: Forever query

Posted: Fri Feb 01, 2008 10:18 am
by Inkyskin
I always index a column that I will be either: using in a WHERE statement, ordering by, or joining.

Re: Forever query

Posted: Sat Feb 02, 2008 5:42 pm
by shiznatix
after reading up on the situation it seams that indexs can very easily speed up this query. i wont have a chance to try this out until monday but i dont see why all columns are not index to begin with? why not eh?

Re: Forever query

Posted: Sat Feb 02, 2008 6:05 pm
by Inkyskin
Indexes can take up quite a lot of space if used too much (IIRC)

Re: Forever query

Posted: Sat Feb 02, 2008 7:50 pm
by Weirdan
shiznatix wrote:i dont see why all columns are not index to begin with? why not eh?
1) They can take a lot of space
2) They slow down queries that update/insert into a table

Re: Forever query

Posted: Mon Feb 04, 2008 5:08 pm
by shiznatix
Holy freaking crap. My 20 second script / query turned into 1 second by adding indexes. Freaking crazy faster. I never heard of indexes before this thread but wow, anyone who has not head of them or used them please try it out.

after talking to a friend over a few beers, my next way to make my queries faster is to look at them through the "explain" command. I never knew SQL had so much to offer.