Left join select speed problem

[Trki]

I am trying this:

PHP Code:

SELECT st.* FROM statuses AS st ORDER BY id DESC LIMIT 10 .. works gr8 ..0,015s...and the result is: 


3556 nick1 status... ...... ......... .......
3555 nick2 status...
3554 nick3 status... ...... ...... ...
3553 nick4 status...
3552 nick5 status...
3551 nick6 status...
3550 nick7 status...
.
.
.

BUT! when i am trying to add next collum here via LEFT JOIN select...

PHP Code:

SELECT st.*,sl.typ FROM statuses AS st
LEFT JO status_likes AS sl
    ON sl.status_id=st.id

 WHERE st.nick='Trki' ORDER BY st.id DESC LIMIT 10IN 


it takes at least 6seconds so verry bad and the result looks bad like this:

3398 status1
3342 status2 the same
3342 status2 the same
3342 status2 the same
3342 ....
3342
3342
3342
.

so .. u can see something is wrong :P What's going bad?

[Fou-Lu]

If its taking a long time to execute, that is an indication of incorrect indexes being set.
So status_linkes.status_id and statuses.id must both be the same datatype and have the same definition, and then make sure both are indexed for faster lookups. You'll never get the same performance as just a single table query, but you shouldn't see a substantial increase either. Also make sure that the nick field on statuses is indexed, but given the 'nick' name for it, that would indicate to me that it's already likely indexed as a unique (but no guarantee since the table name itself doesn't tell me that it has to be unique).

As for the output, that is what you'll get. The language is responsible for formatting it in another logical way. MySQL has a group_concat function that lets you join multiple child records into a single field, but I personally think that's among one of the sillier functions available.

[Trki]

hm... how would you write in mysql this:

Ah i am so messed up from it.

I need select * from statuses LIMIT 30 ... then make a select from status_likes (on one status can be many likes) ...AND SELECT XY likes from status_likes .... where the status_likes.status_id will be the statuses.id ... so

- select 30 statuses
- select XY status likes on this 30 status id.

? :P Everyone who would help i would be so happy !

[Old Pedant]

Show us your COMPLETE table schemas. That is, all the fields and their types for both tables. And if fields are related to one another, show that as well.

[Trki]

Okay ... here are my tables what i need ( i changed my problem so please forget on the previous (its similiar) but lets look at this problem ... how would u solved it? :P )

I have array with this ID of statuses (3398,3342,3129,2895,2157,2072,2062,1995,1945,1915) so i think we can use IN later...

Now i need withdraw all comments from comments table where comments.status_id are from this array with ids...

Then i need select from another table (where the users are) the profile_picture_ultra_small... and connect it with the comments nick names

what is important... its there can be a lot of statuses to one status id ... and just one picture to the nick of person who have commented on. So The tables looks like:

Users .. ( big but.. we need just one collum)



Comments table:



Any ideas? I have solution but is ultra slow....


I wrote hardly something like this

PHP Code:

SELECT c.*,tu.profile_picture_ultra_small
 FROM comments AS c
 LEFT JOIN users AS tu
  ON c.nick = tu.nick
 WHERE status_id IN (3398,3342,3129,2895,2157,2072,2062,1995,1945,1915) 


is that ok? it seems to be ... but .. hm

[Old Pedant]

UGH! VERY VERY BAD!

WHY are you using nick to join the two tables????

For that matter, WHY are you using nick in the COMMENTS table *AT ALL*!!!

You should REPLACE the nick field in COMMENTS with user_id and make it a FOREIGN KEY to the id field in USERS table.

But anyway, the reason it is so slow is that nick is NOT AN INDEX in your COMMENTS table!!!

[Trki]

Originally Posted by Old Pedant

UGH! VERY VERY BAD!

WHY are you using nick to join the two tables????

For that matter, WHY are you using nick in the COMMENTS table *AT ALL*!!!

You should REPLACE the nick field in COMMENTS with user_id and make it a FOREIGN KEY to the id field in USERS table.

But anyway, the reason it is so slow is that nick is NOT AN INDEX in your COMMENTS table!!!

hm... is that so problem ? with the nick... ? :/ btw

but i have index on comments.status_id and i am using it here

WHERE c.status_id IN (3398,3342,3129,2895,2157,2072,2062,1995,1945,1915)

or its useless? rather on nick ... or can i use it on both?

BTW the speed now :

Found rows: 53 Warnings: 0 Duration for 1 query: 0.015 sec. */

[Old Pedant]

I don't think you will get much faster than 15 milliseconds.

As to whether your index of status_id is important or not: How can I tell?

Run EXPLAIN on your query an MySQL will tell you.

[Trki]

Originally Posted by Old Pedant

I don't think you will get much faster than 15 milliseconds.

As to whether your index of status_id is important or not: How can I tell?

Run EXPLAIN on your query an MySQL will tell you.

Hm.. so you think its ok?

btw i have runed explain and the result is



WHat it actually means? :P