|
Left join select speed problem
I am trying this:
PHP Code:
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:
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? |
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. |
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 ! :( |
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.
|
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) http://www.upnito.sk/0/j3amtvbnf7wxp...4a7p8vtku4.png Comments table: http://www.upnito.sk/0/gx276cgzyzd9j...ap3ztzuk72.png Any ideas? :( I have solution but is ultra slow.... I wrote hardly something like this PHP Code:
|
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!!! |
Quote:
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. */ |
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. |
Quote:
btw i have runed explain and the result is http://www.upnito.sk/0/5wwy4bw5kcfrb...at7tf8qc3a.png WHat it actually means? :P |
| All times are GMT +1. The time now is 08:14 AM. |
Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.