Re: Simple query seems to be ignoring date index

[Greg Schubin]

Thanks for the detailed analysis.

I tried LOADing the CSV file to a temporay table then INSERTing into the real table ORDERed By date and a) it works b) it's not unbearably slow and I don't have to do it unless I rebuild the entire table. It also works with the daily updates I do, although the data might eventually get scattered again.

I have InnoDB turned off because I originally used the default /var/lib for the database. It had 2 gigabytes available which filled up pretty quickly. When I discovered the InnoDB files were over 1 gig I deleted them and turned it off. The parition filled up again and I dedicated a 100 gig hard drive to the database. I turned InnoDB on again but it had problems so I turned it off. Probably something in my.cnf but I don't feel like delving into that right now.

The SELECT on 'date' is the one that gets called most often. I also have a PRIMARY KEY on (Symbol,Date) but that is mainly to ensure no duplicates. I don't even think I need the sym index.

ADD: I just dropped the Date index and reversed the PK to (Date,Symbol) and the Date lookup is just as fast.



Edited 1 time(s). Last edit at 11/02/2013 09:55PM by Greg Schubin.