Decoux: Scott0_: well, the tmp table could be large.
Decoux: Scott0_: you have to use EXPLAIN on it, and test it.
Sommella: Yeah it could be big considering column d would restrict it to a few hundred records
Hoesly: And without that, a and b alone would be huge
Olivieri: Ok thanks for refreshing my memory
Kint: Its already and 8GB index :
Decoux: Right – you have to weight the tradeoff of each approach
Decoux: If rewriting your sql is a possibility, it may be a better approach
Coast: The query or the structure?
Wirtjes: Can’t do the structure
Chilinskas: But no matter which angle on the query, I still don’t have the column d that I need
Decoux: Scott0_: and depending on your use case, an inverted index may help
Bawer: So I guess I have no choice
Rossiter: Scott0_: http://code.google.com/p/inverted-index/
Decoux: Scott0_: it’s usually a decent replacement for full text search
Lorenz: Prob be easier to increase the server disk space and add the index
Eaby: Or just wait forever for each query
Risper: Would the temp table searching for d at least be cached?
Dolfi: Mysql does cache repeated queries
Decoux: Scott0_: you can’t rely on the query cache for write-heavy schemas
Rosborough: Its a data logging server
Donatello: Takes csv files abd breaks them down into values which are stored as individual rows
Greenlaw: My biggest table is the values table
Determann: Lots of records, but hardly write heavy
Prenatt: Data flows in slowly, and then its read out in large chunks
Decoux: Scott0_: the query cache doesn’t cache subqueries – it would cache the entire sql, if the cache hasen’t been invalidated.
Moton: Ok then that’s not an option
Jinright: Looks like im in for another 8GB of indexes
Furr: Would it double up on the index? or could one index leverage another?
Decoux: Scott0_: run EXPLAIN on it first.
Katayama: I also ran show table status to get the current index size
Yamaoka: Decoux: mind looking at it?
Decoux: Scott0_: I only have a few minutes, but show it quickly.
Noice: Decoux: http://pastie.org/pastes/10420048/text
Castellaw: The table has somewhere of 300M rows
Decoux: Scott0_: the full scan on device_columns is fine
Decoux: Scott0_: full table scan key = NULL
Korner: There’s only a handful
Scharnberg: My primary concern is points
Decoux: Scott0_: your index on points isn’t that effective, or you have too much data that matches your range condition.
Knieriem: Which is where my 8GB index exists
Decoux: Scott0_: do you really have 13M rows that set in the datetime range you selected?
Cormican: I didn’t set a datetime range
Decoux: Scott0_: you did, apparently.
Peters: That’s the one columns from the index I don’t need to use
Minerd: My search is for a,b,d
Decoux: Scott0_: ah right, it’s not using an index.
Mccomack: So its using that index for part of the search
Decoux: Scott0_: how long does it take to run?
Brar: I haven’t let it run its course
Demers: How do I get it to run without showing me the returned rows?
Wimberly: Just to get a time of run?
Decoux: Scott0_: you can pipe the results to /dev/null, but that won’t save you much time.
Decoux: Scott0_: count* could be a bit faster, yes.
Mense: Id just rather not have the console try to list the rows