I'd like to ask the forum's advice regarding improving the query. data is being inserting from logs. there are a 21 columns in table. the most requiring field is
"Time", the data type of Time is "int (11").

when i run query "select time from db1" , its result is ,

1209839702
1209839685
1209839686
1209839016
1209839922
1209837933
1209837863

where as when i run the query

select id , date_format(from_unixtime(time), '%d-%m-%Y %H:%i') between '01-01-2008 03:01' and '30-06-2008 07:06' group by products;


it takes 20 minutes to complete.

Is there any solution to minimize the time. even 100 hash partitions didn't make any improvement.

total rows are 6,00,00,000.

thanks in advance for your advice and suggestions.

Just out of curiousity, can you explain what you want the query to do using English instead of code? I can't imagine what this query ought to do, so I'm having trouble deciding how to help you optimize it.

One thing that might help get you onto a more productive track would be to experiment with something like:-PatP

I want to get all fields based on time column,

the problem increases, because when i query for specific date/time e.g
1209839702 then every tuple have to converted and matched against defined time in query.and i think this is the reason of delay .


I just want to get this from DB,

even this take 8 minutes to complete.

sounds like you might want to have an index on the `time` column

I think that between my suggestion of using the unix_timestamp() function and r937's suggestion of putting an index on the time column ought to get you a query that performs much better.

Even with six crore rows in the table, I'd expect retrieval times for a single row to be well under one second.

-PatP

Yes Phelan you are right.

after adding index on time column and using unix_timstamp() i got 0.01sec to get a single record.

one more help from you,



when i use between 1199134860 and 1214831160 , it takes time and
explain ,
can you help me in this .
thanks for your previous response , i highly appreciate your response.

Gr8

how often are you going retrieve

1. data for all names in a specified time interval

versus how often are you going retrieve

2. data for a specific name in a specified time interval

versus how often are you going retrieve

3. data for a specific name in all time intervals

whichever of these is the most frequent query will decide what type of index you need

your first query was type 1, your latest query was type 2

No. 3 matches near the condition, name can be changed but there are total 60 names and time will be vary depends upon requirements.
the time i specified was from 2008-01-01 to 2008-06-30 . but the end user will select the time from application.
so i created index on "time"

i got improvement on this and on my first query it toked 20 minutes, then i installed a slave server and got the result in 9 minutes after adding indexes and unixtimestamp() it got the result in 6 minutes . but still it need to be fast .................

thanks for response.

oaky, your index should be on {name,time}

You aren't limited to just one index per table. Indicies do have a cost in terms of disk space and additional processing for changes (INSERT, UPDATE, and DELETE operations), but they can drastically improve retrieval (SELECT operations).

I'd suggest two indicies for what I know so far of your scenario. One index on time alone, the other on name and time (in that order).

As you add more complex operations, you may need to review your indicies and make changes, but I think this should be a good start.

-PatP

thanks all of you people,

With reference to "High Performance MySQL Optimization, Backups, Replication, Load Balancing & More By Jeremy Zawodny, Derek J. Balling
April 2004
Pages: 294
ISBN 10: 0-596-00306-4 | ISBN 13: 9780596003067 "


Chapter 5.2
"
Even if you have a lot of data (thousands of rows or more), MySQL may choose to ignore your indexes some of the time if your data doesn't have sufficient diversity. Why might that happen? Imagine you have a table that contains historical climate data for most world cities:

CREATE TABLE weather

(
city VARCHAR(100) NOT NULL,
high_temp TINYINT NOT NULL,
low_temp TINYINT NOT NULL,
the_date DATE NOT NULL,
INDEX (city),
INDEX (the_date),
)
Rather than loading all two million records, you load two years worth of data (1980 and 1981) to test. After some testing, you find that queries that need to access many of the records are using full table scans rather than the the_date index. For example, to find the average high temperature in 1980, you might write something like this:

SELECT AVG(high_temp) FROM weather

WHERE the_date BETWEEN '1980-01-01' AND '1980-12-31';
Having data from only 1980 and 1981 loaded, that query needs to examine 50% of the rows in the weather table. In such a case, MySQL decides that it is faster to simply scan the entire table."


I would like to know how do i get performance.