108 reputation
10
bio website matt.coneybeare.me
location New York, NY
age 33
visits member for 3 years, 2 months
seen Jan 24 '13 at 13:26

http://matt.coneybeare.me
@coneybeare

I have had a ton iOS apps under my name and my business name. I have multiple client apps, and one from an established startup. Many of my apps have been featured by Apple. I developed two iOS apps that made it into the top 100. One of the apps held the number two spot for three weeks. Another floats in/out of the paid News top 10. My apps have over 10 million downloads.

I do a ton of web work too using rails for all my sites. I do much of my own design for all the sites I have created.


Jul
14
awarded  Popular Question
Jul
13
awarded  Popular Question
Jan
20
comment Very Slow MySQL Queries, even with indexes
I opened a near identical question on StackOverflow. The traffic there is just much higher: stackoverflow.com/questions/14418134/…
Jan
19
comment Very Slow MySQL Queries, even with indexes
Well, you didn't solve the problem, but you helped and I would hate the 50 bounty to be wasted, so I awarded it to you in the absence of another answer.
Jan
19
awarded  Benefactor
Jan
14
comment Very Slow MySQL Queries, even with indexes
Actually, the index it is using already is a combined index, (LO.timestamp, LO.deleted_at, LO.cloud_log_id) and the optimizer is choosing to use it over the others.
Jan
14
comment Very Slow MySQL Queries, even with indexes
Doing it with a subquery table definitely helped, but it is still at 17 seconds. The things you have suggested are definitely shaving time off of the execution when implemented, but i feel we are not getting to the root of the problem here.
Jan
14
revised Very Slow MySQL Queries, even with indexes
added 365 characters in body
Jan
14
comment Very Slow MySQL Queries, even with indexes
Well, the table is using the index properly, as shown by the EXPLAIN, and I have already analyzed and optimized the tables. I will try to use your 3rd approach, but I am not working with SQL directly but through rails, so it is tougher to do stuff like this.
Jan
14
revised Very Slow MySQL Queries, even with indexes
added 197 characters in body
Jan
14
comment Very Slow MySQL Queries, even with indexes
Ok, the previous cut time only dealt with the 2nd query, the longer one still took 70 seconds to run. Upon new tests, it seems the 25 seconds may have been an anomaly because I can no longer get it that low. I have added a deleted_at_cloud_log_id on LO and that didn't help much, but I have concluded that removing the ordering clause in the SQL causes it to execute in under 3 seconds. So, I will add a deleted_at_cloud_log_id_timestamp index and see if that helps, then report back
Jan
13
comment Very Slow MySQL Queries, even with indexes
This is an interesting idea. New logs come in as the most recent though, so it seems like I would have to continually and manually add partitions as the "new" partition gets excessively large.
Jan
12
comment Very Slow MySQL Queries, even with indexes
2. Timestamp was indexed. Adding deleted_at index took it down to 25 seconds, but it still needs further work
Jan
12
comment Very Slow MySQL Queries, even with indexes
1. Turns out i did not have an index on deleted_at.
Jan
11
awarded  Promoter
Jan
9
asked Very Slow MySQL Queries, even with indexes
May
22
comment How can I optimize this MySQL query further?
Your update at 07:51 produced this explain: cloud.coneybeare.net/6xk6 I think that the original call, with the additional index that you suggested has optimized the best by reducing the number of rows
May
22
comment How can I optimize this MySQL query further?
None of the updates made it better, but adding the index that depended on the keys helped a fraction so I will give you the answer. Maybe this call just cannot be optimized further?
May
22
awarded  Scholar
May
22
awarded  Supporter