fenway

Yes, you'd need a JOIN for each of the N levels of the hierarchy that you'd want to display... provided that N is small (<5), you're ok.

Equivalent set of functions can be used in mysql directly... LOWER, UPPER with SUBSTR, etc.

What do you want to optimize?

What I mean is that the join conditions are applied *before* the where clause is applied. If you have a three table join -- all inner joins -- it will try and find matching rows in each of the subsequent tables ("sweeping join plan") and "combine" (a.k.a. join) that record with its matching record(s). However, if any condition fails, it will discard that record before even looking at the subsequent tables and join conditions. That is, non-matching records from the join are eliminated early on in statement execution. On the other hand, If it's all left joins, this is NEVER possible, since you need to keep all the "NULL records", because presumably you need them (e.g. for an is null check, etc.). The WHERE clause is only applied after all of the tables are joined... so you end up (potentially) collecting a lot more records than you need with left join, only to throw them out later on!

This is as good as you can get -- you're using the index, no filesort... "rows" doesn't account for limit, as mentioned above. For comparison, drop the index on posts, and you'll see how long it actually would take to order 30K rows backwards! ;-)

Personally, I prefer something similar to the following for age: "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0"... easier to read, no string hacks! You need to take your age query, add the uid to the select list, and then inner join that resulting table to te query you posted above with the restriction you desire in your where clause.

Sorry, perhaps I misread your post -- does the EXPLAIN show "using filesort" or not? The 30k+ rows doesn't mean anything, it doesn't take LIMIT into account.

You shouldn't ever use array indicies to find your columns... use the name of the column by asking for a hash. Besides, if you ask from "SELECT name, id FROM...", you'd get them reversed!! That makes more sense... yes, SELECT without ORDER BY will return the rows in arbitrary order -- if it looks "ordered", that's because (a) you're using an auto-increment field AND b) you haven't deleted and then inserted new records after optimizing the table. It's not robust -- if you want or expect an order, use an explicit ORDER BY clause.

I'm not sure what you mean by "the same"... if you use mysql_fetch_assoc(), you're asking for a hash, so the order is undefined (or should be). If you use mysql_fetch_array(), you get them back "in order"... but this means different things depending on the number of tables you have, the type of join, the join conditions (ON vs USING), etc. In any case, you shouldn't *EVER* base anything on this order at all... and moreover, there can't possible be any need to.

Sounds like you need to join the tables...

Sometimes that becuase the column type doesn't match.

Index order can't get specified yet... in principle, you can physically alter the table with a given ordering, but that won't help as soon as your table changes...

Except that an inner join will stop "joining" as soon as one of the join tables fails, while a left join has to keep joining as doesn't use the where clause until the end -- which can get very expensive if you only want matching rows anyway!

Me too... what do you want in the final output?

You'll have to write a function that knows what's needs escaping...

From which query? What does this produce( from above): SELECT `BTBS_Activites`.`VID`, AVG(`BTBS_Activites`.`Result`) as moyenneVote FROM `BTBS_Activites`,`BTBS_Videos` WHERE `BTBS_Activites`.`Action` = 'Vote' AND `BTBS_Videos`.`State` = 'Online' AND `BTBS_Activites`.`VID` = `BTBS_Videos`.`ID` GROUP BY `BTBS_Activites`.`VID` ORDER BY moyenneVote DESC

Read the sticky on normalization... there's an article in there about storing hierarchical data.

How are you just getting a single VID from a GROUP BY?

Could you post the query too...? I'm not liking that temp/filesort at this point... As far as LEFT vs INNER, unless one of the intermediate tables might not match, LEFT is going to be much slower.

You expected a reply at 2AM?

GROUP_CONCAT() may help you out.

Hard to say... the joining columns should definitely be indexed. Post the EXPLAIN for your current query. Also, unless you really need LEFT JOIN, INNER JOIN is often much, much faster.

Yes, you need a self-join to the same table (differnet) alias using t1.parent_id = t2.id in the on clause.

What do you mean by "you don't know"... is this value in the db or not?

Why don't you want the ID back? Why does it matter?