30 reputation
117
bio website
location
age
visits member for 2 years, 1 month
seen Dec 11 '12 at 5:01

Jun
9
awarded  Famous Question
Jun
4
awarded  Popular Question
Apr
14
awarded  Notable Question
Feb
11
awarded  Popular Question
Dec
11
comment Slow query with straight_join for small result
But in my case I do a lot of bulk inserts to import new data (every 10 minutes). And the result of my performance-tests was that mysql slows down very hard without this OPTIMIZE-frequence.
Dec
9
awarded  Commentator
Dec
9
comment Slow query with straight_join for small result
My “problem” is that I don’t know the “better” plan than the optimizer. Especially I don’t understand why the “fast” straight_join-query is so slowly for querys (profiles) with less (or no) data in the mentioncache-table (just testet: for a small profile the normal-join query is about 300 times faster!). Do you have any idea for this huge difference?
Dec
9
comment Slow query with straight_join for small result
Hi Michal, thanks for your reply. I added the explain for the „normal“ join query. You are right: In this case MySQL uses a different query plan: In the straight_join-case the mention-table is the first table and the mentioncache-table is the last table. In the normal join-case the mentioncache table is the first (!) table an the mention-table the second one.
Dec
9
comment Slow query with straight_join for small result
Hi Rick, thanks for your reply. I do a optimize table per cron every 2-4 hours, so I think the statistics of the table should be up-to-date.
Dec
9
revised Slow query with straight_join for small result
added 968 characters in body
Dec
4
asked Slow query with straight_join for small result
Nov
8
awarded  Tumbleweed
Oct
19
asked MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time?
Oct
18
comment Slow insert with MySQL full-text index
Sorry - don't seen your comment. The table has about 30 varchar and text fields, some indexes on varchar-fields and a fulltext index over the three fields title_text (char(255)), content_text (text), author_text (char(255)). In the table are about 210.000 data sets, the table size is about 2,5 GB and the index-size about 0,5 GB. The insert queries are done for every new data set (no combined query for all inserts). Do you need more informations?
Oct
17
revised Slow complex query with group/order
Remove some errors in query
Oct
17
suggested suggested edit on Slow complex query with group/order
Oct
17
comment Slow complex query with group/order
I don't know why but the query is even slow just with one more join table. I tested it with the attachment-table and the visiteditems-table - in both cases the query performs really slow. It seems that the structure of the joins with the subquery is very slowly. With a "normal" join without the subquery the performance is much better.
Oct
17
asked Slow insert with MySQL full-text index
Oct
16
comment Slow complex query with group/order
Thanks for your tips! Unfortunately even a simpler version of your query (without the case when construct and the join on the mentionfeedscores-table) returns a result after 400 (!) seconds. Do you have any idea why the performance is so bad?
Oct
16
comment Slow complex query with group/order
Seems that index-changing in the mentionfeedscores-table does not change the performance of this query.