Id just rather not have the.

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_:

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:

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