[14:15] <dorgan> hey guys
[14:17] <dorgan> I'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 data
[14:17] <dorgan> *I've asked
[14:29] <mhall119> dorgan: it's the filesort that's killing you
[14:29] <mhall119> which could just mean that the first set of tables has too much data to perform the sort in memory
[14:30] <mhall119> can you increase the amount of memory allocated to MySQL?
[14:30] <dorgan> yeah but why wouldnt it use that other index
[14:30] <dorgan> there already over 4.5 GB allocated :)
[14:32] <dorgan> could it be the join buffer?
[14:32] <mhall119> hmm, 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 trying
[14:32] <dorgan> yeah i tried that
[14:32] <dorgan> :D
[14:33] <mhall119> it looks like you're duplicating clauses between your join and your where, is that necessary?
[14:36] <dorgan> probably not...but I dont think its hurting
[14:55] <tiemonster> dorgan: can you switch the WHERE's to INNER JOINs?
[14:55] <dorgan> not sure if that is going to help
[14:56] <dorgan> i am actually going to try and rebuild the orders table
[14:56] <dorgan> this way I can rebuild the indexes on it
[15:02] <dorgan> hmmm that didnt seem to help
[15:02] <dorgan> should i be running a flush tables after rebuilding tables and renaming them?
[15:03] <dorgan> meaning moving the old out to a back up table and then swapping in the new one
[15:07] <dorgan> tiemonster: what did you mean switch the WHERE's to INNER JOINS
[15:07] <dorgan> ?
[15:07] <dorgan> they pretty much already are...they're just duplicated
[15:07] <tiemonster> the tables you're WHEREing against
[15:07] <tiemonster> JOINs are by default LEFT, correct?
[15:07] <tiemonster> or are they INNER?
[15:09] <dorgan> INNER BY default
[15:09] <dorgan> I believe
[15:15] <dorgan> sometimes i hate mysql
[15:15] <dorgan> lol
[17:14] <RoAkSoAx> itnet7: ping ping ping
[17:14] <RoAkSoAx> itnet7: ping ping ping
[17:14] <RoAkSoAx> itnet7: ping ping ping ping ping
[18:22] <mhall119> itnet7: I think RoAkSoAx wants your attention
[18:37] <RoAkSoAx> mhall119: just wanted to nag him :)
[18:44] <maxolasersquad> Anyone else randomly not have their drop-down menus in Unity?
[18:56] <mhall119> nope
[19:02] <maxolasersquad> http://maxolasersquad.com/screenshot.png
[19:02] <maxolasersquad> No menus.
[19:03] <maxolasersquad> I've had in too when everything is minimized and I'm trying to access the desktop menus.
[19:24] <danstoner> dorgan: still looking for sql help?
[19:24] <dorgan> somewhat
[19:24] <dorgan> i ahvent made any progress :)  but may be going another route...but would love to solve it
[19:25] <danstoner> I'm guessing the date BETWEEN is forcing the sequential scan.
[19:34] <maxolasersquad> dorgan: It looks like the to= statements are being duplicated.  They are in both the join and where clauses.
[19:35] <dorgan> yes it doesnt have any effect
[19:35] <dorgan> I've tried just having them in the individual places
[19:35] <dorgan> its just a duplication of a statement
[19:39] <danstoner> dorgan: I'm reading about mysql query performance... apparently calling a function on a field means an index will not be used.
[19:40] <danstoner> date(create_date)
[19:40] <danstoner> means even adding an index on create_date won't help you (if that is still true).
[19:40] <dorgan> I dont use that in a where though
[19:41] <dorgan> or a join
[19:41] <danstoner> True dat.
[19:41] <dorgan> its used in the select and the group
[19:41] <dorgan> :)
[19:44] <maxolasersquad> dorgan: You are using datediff in the WHERE clause.
[19:44] <maxolasersquad> I know in Oracle SQL BETWEENs can be performance killers.
[19:45] <maxolasersquad> Also, play around with the order you JOIN the tables.  Start with smaller tables and go bigger.
[19:45] <maxolasersquad> I've had this make a big difference in the past.
[19:46] <maxolasersquad> Though it may be worth your while to play around.  I've had joining tables in unexpected orders help from time-to-time.
[19:46] <maxolasersquad> Also, moving expensive operators in the WHERE clause can tend to speed things up.
[19:46] <dorgan> yeah thats what I did
[19:47] <dorgan> the problem is the bb table
[19:47] <maxolasersquad> Any parent you join on should be indexed.
[19:49] <maxolasersquad> Which it looks like you are already doing.
[19:50] <maxolasersquad> This may sound stupid, but try WHERE create_date > '2011-07-01 00:00:00' AND create_date < '2011-07-31 23:59:59'
[19:51] <maxolasersquad> I'm with danstoner in believing the WHERE clause is the mostly likely cause of your problem.
[19:52] <maxolasersquad> Depending on the size of the result set, the GROUP BY could be problematic as well.
[19:52] <maxolasersquad> Grouping and ordering large data sets can be very costly.
[19:54] <maxolasersquad> Anyways, those are the only things I can think of looking at your query and CREATE statements.  I'm outta here.
[20:06] <dorgan> maxolasersquad: yeah that doesnt make a change :/
[20:07] <dorgan> ahh like i said I think we're going a different route and not going to look at this stuff in realtime
[20:07] <dorgan> we're going to create a process that update other data with what this supposed to show