144 reputation
4
bio website ubimind.net
location Paris, FR
age 34
visits member for 1 year, 8 months
seen Aug 27 at 15:07

IT Engineer, Opensource CMS addict since 1998, Satch fan, proud js1200 owner!, XNA noob, OAuth&OpenID curious, amateur photographer, motorcycle passionate.

See my career profile here : http://careers.stackoverflow.com/melvin


Jan
24
comment Very Slow MySQL Queries, even with indexes
Ok I'll go check this out, thanks!
Jan
20
comment Very Slow MySQL Queries, even with indexes
Hi there, thanks for the bounty. I'm a bit surprised noone else answered the question; don't hesitate to post an update if you come up with a solution. I'm going to forward this to Peter ZAITSEV (mysqlperformanceblog), in case he has time to have a look.
Jan
20
awarded  Supporter
Jan
15
comment Very Slow MySQL Queries, even with indexes
Doh! Great detailed explanation on the order-by here : dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html but 1:20am here, going to sleep zZz. Hopefully a true MySQL DBA is going to help; I'd like to get the solution explained as well, other than tweaking step by step.
Jan
14
comment Very Slow MySQL Queries, even with indexes
Ok, given this discussion: stackoverflow.com/questions/2939289/… , you should DROP your index on LO.timestamp and set an index on (LO.timestamp, LO.deleted_at) instead.
Jan
14
comment Very Slow MySQL Queries, even with indexes
Yes I feel the same ahah : the root of the problem may have to do with this temporary/filesort which I guess is working on a full scan. Going to investigate on this as I'm not familiar with that. You're definitely not the only one struggling with order by/limit in mySQL as I can see given the amount of pages on this topic on the web.
Jan
14
comment Very Slow MySQL Queries, even with indexes
To answer your last question, quoting the official MySQL doc : If you have a problem with indexes not being used when you believe that they should be, run ANALYZE TABLE to update table statistics, such as cardinality of keys, that can affect the choices the optimizer makes. See Section 13.7.2.1, “ANALYZE TABLE Syntax”. But see my step 3 in my answer, try it see if it helps.
Jan
12
awarded  Editor
Jan
12
comment Very Slow MySQL Queries, even with indexes
Ok I edited my answer and added some stuff to help.
Jan
12
revised Very Slow MySQL Queries, even with indexes
added 1941 characters in body
Jan
11
answered Very Slow MySQL Queries, even with indexes
Jan
10
comment Modify MySQL foreign key related column without dropping foreign key relation
From what I understood Praveen wants to do some work on a column that has a foreign key constraint, and that without actually dropping the constraint; but maybe I got this wrong ?
Jan
10
answered Modify MySQL foreign key related column without dropping foreign key relation
Jan
7
awarded  Teacher
Jan
7
answered Update many-to-many relationship table
Jan
6
answered Is there a name for this database structure?
Jan
6
awarded  Autobiographer