Hi,
Edward Meyer wrote:
>
> I have a table with the following columns
> id => int not null auto_increment
> stockid => integer
> tdate => date
> price => float
> primary key (id)
> key (productid)
>
> The table has 8,000,000+ rows holding 8000+ different stockids.
>
> I have to pull various information from the table the first query is to get
> the average
> price for the last 10 days.
>
> select avg(price) from table1 where tdate>date_sub(today, interval 10 day)
>
> 1. How can I optimize this query?
> 2. Are there any advantages to using an index on the tdate column?
Yes. MySQL will use an index on tdate if it exists.
Check http://www.mysql.com/doc/M/y/MySQL_indexes.html for details.
If you don't create an index, MySQL will do a full table scan !
You should also check your query with EXPLAIN, it will show you if
an index is used or not.
>
> Secondly, I need to get all stockids which have today's price greater than
> yesterday's.
>
> Here is how I do this now I am looking for a single statement if
> possible.
>
> select price where tdate=yesterday and stockid=xxx
> select price where tdate=today and stockid=xxx
> compare selected values
>
> 1. Is this possible with one select?
May be:
SELECT a.stockid, a.price, b.price
FROM table as a, table as b
WHERE a.stockid=b.stockid
AND a.tdate=yesterday
AND b.tdate=today
AND a.price<b.price
> 2. Best way to optimize the table for quickest access to the data?
I would try an unique index on (stockid,tdate) or (stockid,tdate,price)
Again, EXPLAIN is your friend ;)
>
> Thanks in advance.
>
> Edward
>
Hope this helps
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
