| dorgan | hey guys | 14:15 |
|---|---|---|
| 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:17 |
| 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:29 |
| 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:30 |
| 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:32 |
| mhall119 | it looks like you're duplicating clauses between your join and your where, is that necessary? | 14:33 |
| dorgan | probably not...but I dont think its hurting | 14:36 |
| tiemonster | dorgan: can you switch the WHERE's to INNER JOINs? | 14:55 |
| dorgan | not sure if that is going to help | 14:55 |
| dorgan | i am actually going to try and rebuild the orders table | 14:56 |
| dorgan | this way I can rebuild the indexes on it | 14:56 |
| dorgan | hmmm that didnt seem to help | 15:02 |
| dorgan | should i be running a flush tables after rebuilding tables and renaming them? | 15:02 |
| dorgan | meaning moving the old out to a back up table and then swapping in the new one | 15:03 |
| 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:07 |
| dorgan | INNER BY default | 15:09 |
| dorgan | I believe | 15:09 |
| dorgan | sometimes i hate mysql | 15:15 |
| dorgan | lol | 15:15 |
| RoAkSoAx | itnet7: ping ping ping | 17:14 |
| RoAkSoAx | itnet7: ping ping ping | 17:14 |
| RoAkSoAx | itnet7: ping ping ping ping ping | 17:14 |
| mhall119 | itnet7: I think RoAkSoAx wants your attention | 18:22 |
| RoAkSoAx | mhall119: just wanted to nag him :) | 18:37 |
| maxolasersquad | Anyone else randomly not have their drop-down menus in Unity? | 18:44 |
| mhall119 | nope | 18:56 |
| maxolasersquad | http://maxolasersquad.com/screenshot.png | 19:02 |
| maxolasersquad | No menus. | 19:02 |
| maxolasersquad | I've had in too when everything is minimized and I'm trying to access the desktop menus. | 19:03 |
| 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:24 |
| danstoner | I'm guessing the date BETWEEN is forcing the sequential scan. | 19:25 |
| maxolasersquad | dorgan: It looks like the to= statements are being duplicated. They are in both the join and where clauses. | 19:34 |
| 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:35 |
| danstoner | dorgan: I'm reading about mysql query performance... apparently calling a function on a field means an index will not be used. | 19:39 |
| 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:40 |
| dorgan | or a join | 19:41 |
| danstoner | True dat. | 19:41 |
| dorgan | its used in the select and the group | 19:41 |
| dorgan | :) | 19:41 |
| maxolasersquad | dorgan: You are using datediff in the WHERE clause. | 19:44 |
| maxolasersquad | I know in Oracle SQL BETWEENs can be performance killers. | 19:44 |
| 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:45 |
| 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:46 |
| dorgan | the problem is the bb table | 19:47 |
| maxolasersquad | Any parent you join on should be indexed. | 19:47 |
| maxolasersquad | Which it looks like you are already doing. | 19:49 |
| 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:50 |
| maxolasersquad | I'm with danstoner in believing the WHERE clause is the mostly likely cause of your problem. | 19:51 |
| 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:52 |
| maxolasersquad | Anyways, those are the only things I can think of looking at your query and CREATE statements. I'm outta here. | 19:54 |
| dorgan | maxolasersquad: yeah that doesnt make a change :/ | 20:06 |
| 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 | 20:07 |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!
