dorganhey guys14:15
dorganI've asking in #mysql and no 1 seems to a) be able to help or b) want to help  so i figured i would ask in here because I know some of you also deal with mysql:  for some reason this query is taking a very long time and doesnt seem to be using the correct indexes...I've tried an analyze table on all the tables involved:   http://pastie.org/private/wtjfazkedihyrvaoyddu3q     whats funny is we have other table of course with different data14:17
dorgan*I've asked14:17
mhall119dorgan: it's the filesort that's killing you14:29
mhall119which could just mean that the first set of tables has too much data to perform the sort in memory14:29
mhall119can you increase the amount of memory allocated to MySQL?14:30
dorganyeah but why wouldnt it use that other index14:30
dorganthere already over 4.5 GB allocated :)14:30
dorgancould it be the join buffer?14:32
mhall119hmm, the way MySQL chooses index is funny sometimes, I think there's a way in your query to tell it which indexes it should be trying14:32
dorganyeah i tried that14:32
mhall119it looks like you're duplicating clauses between your join and your where, is that necessary?14:33
dorganprobably not...but I dont think its hurting14:36
tiemonsterdorgan: can you switch the WHERE's to INNER JOINs?14:55
dorgannot sure if that is going to help14:55
dorgani am actually going to try and rebuild the orders table14:56
dorganthis way I can rebuild the indexes on it14:56
dorganhmmm that didnt seem to help15:02
dorganshould i be running a flush tables after rebuilding tables and renaming them?15:02
dorganmeaning moving the old out to a back up table and then swapping in the new one15:03
dorgantiemonster: what did you mean switch the WHERE's to INNER JOINS15:07
dorganthey pretty much already are...they're just duplicated15:07
tiemonsterthe tables you're WHEREing against15:07
tiemonsterJOINs are by default LEFT, correct?15:07
tiemonsteror are they INNER?15:07
dorganINNER BY default15:09
dorganI believe15:09
dorgansometimes i hate mysql15:15
RoAkSoAxitnet7: ping ping ping17:14
RoAkSoAxitnet7: ping ping ping17:14
RoAkSoAxitnet7: ping ping ping ping ping17:14
mhall119itnet7: I think RoAkSoAx wants your attention18:22
RoAkSoAxmhall119: just wanted to nag him :)18:37
maxolasersquadAnyone else randomly not have their drop-down menus in Unity?18:44
maxolasersquadNo menus.19:02
maxolasersquadI've had in too when everything is minimized and I'm trying to access the desktop menus.19:03
danstonerdorgan: still looking for sql help?19:24
dorgani ahvent made any progress :)  but may be going another route...but would love to solve it19:24
danstonerI'm guessing the date BETWEEN is forcing the sequential scan.19:25
maxolasersquaddorgan: It looks like the to= statements are being duplicated.  They are in both the join and where clauses.19:34
dorganyes it doesnt have any effect19:35
dorganI've tried just having them in the individual places19:35
dorganits just a duplication of a statement19:35
danstonerdorgan: I'm reading about mysql query performance... apparently calling a function on a field means an index will not be used.19:39
danstonermeans even adding an index on create_date won't help you (if that is still true).19:40
dorganI dont use that in a where though19:40
dorganor a join19:41
danstonerTrue dat.19:41
dorganits used in the select and the group19:41
maxolasersquaddorgan: You are using datediff in the WHERE clause.19:44
maxolasersquadI know in Oracle SQL BETWEENs can be performance killers.19:44
maxolasersquadAlso, play around with the order you JOIN the tables.  Start with smaller tables and go bigger.19:45
maxolasersquadI've had this make a big difference in the past.19:45
maxolasersquadThough it may be worth your while to play around.  I've had joining tables in unexpected orders help from time-to-time.19:46
maxolasersquadAlso, moving expensive operators in the WHERE clause can tend to speed things up.19:46
dorganyeah thats what I did19:46
dorganthe problem is the bb table19:47
maxolasersquadAny parent you join on should be indexed.19:47
maxolasersquadWhich it looks like you are already doing.19:49
maxolasersquadThis may sound stupid, but try WHERE create_date > '2011-07-01 00:00:00' AND create_date < '2011-07-31 23:59:59'19:50
maxolasersquadI'm with danstoner in believing the WHERE clause is the mostly likely cause of your problem.19:51
maxolasersquadDepending on the size of the result set, the GROUP BY could be problematic as well.19:52
maxolasersquadGrouping and ordering large data sets can be very costly.19:52
maxolasersquadAnyways, those are the only things I can think of looking at your query and CREATE statements.  I'm outta here.19:54
dorganmaxolasersquad: yeah that doesnt make a change :/20:06
dorganahh like i said I think we're going a different route and not going to look at this stuff in realtime20:07
dorganwe're going to create a process that update other data with what this supposed to show20:07

Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!