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