Forever query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Forever query

Post 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.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Forever query

Post by shiznatix »

*bump* anyone know why such a simple left join would cause the query to slow down 200x plus?
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: Forever query

Post by Inkyskin »

Are the the columns rt.room_username, um.room_username, rt.fk_room_id, um.fk_room_id indexed?
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Forever query

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Forever query

Post 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.
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: Forever query

Post by Inkyskin »

I always index a column that I will be either: using in a WHERE statement, ordering by, or joining.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Forever query

Post 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?
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: Forever query

Post by Inkyskin »

Indexes can take up quite a lot of space if used too much (IIRC)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Forever query

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Forever query

Post 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.
Post Reply