Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2008
    Posts
    24

    Unanswered: query problem/performance enhancement

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    SELECT id , date_format(from_unixtime(time), '%d-%m-%Y %H:%i')
       FROM db1
       WHERE time BETWEEN unix_timestamp('2008-01-01 03:01') and unix_timestamp('2008-06-30 07:06')
    -PatP

  3. #3
    Join Date
    Aug 2008
    Posts
    24
    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,

    PHP Code:
    SELECT id date_format(from_unixtime(time), '%d-%m-%Y %H:%i')
       
    FROM db1
       WHERE time unix_timestamp
    ('2008-01-01 03:01'
    even this take 8 minutes to complete.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like you might want to have an index on the `time` column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Aug 2008
    Posts
    24
    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,

    PHP Code:
    mysqlselect  count(*) as callssum(duration) as minutesname from  db1 where name='gateway' and time between 1199134860 and 121483116 

    PHP Code:
    mysqlexplain extended select  count(*) as callssum(duration) as minutesname from db1 where name='gateway' and time between 1199134860 and 1214831160 ;                                         +----+-------------+----------+------+---------------+------+---------+------+----------+----------+----------------------------------------------+
    id select_type table    type possible_keys key  key_len ref  rows     filtered Extra                                        |
    +----+-------------+----------+------+---------------+------+---------+------+----------+----------+----------------------------------------------+
    |  
    SIMPLE      db1 ALL  stime         NULL NULL    NULL 56390208 |    33.31 Using whereUsing temporaryUsing filesort |
    +----+-------------+----------+------+---------------+------+---------+------+----------+----------+----------------------------------------------+
    1 row in set1 warning (0.00 sec

    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2008
    Posts
    24
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oaky, your index should be on {name,time}
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  11. #11
    Join Date
    Aug 2008
    Posts
    24

    Thumbs up

    thanks all of you people,

  12. #12
    Join Date
    Aug 2008
    Posts
    24

    index and date

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •