SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Slow Query...

  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow Query...

    Hi all ,

    I recently started learning MYSQL :

    There are two tables in my database

    Table A - 562712 Records

    'Link', 'varchar(50)', 'YES', '', NULL, ''
    'Feature', 'varchar(50)', 'YES', '', NULL, ''
    'Chromosome', 'varchar(20)', 'YES', '', NULL, ''
    'Start', 'int(11)', 'YES', '', NULL, ''
    'End', 'int(11)', 'YES', '', NULL, ''

    Table B - 1240 Records
    Start, int(11), NO, , ,
    End, int(11), NO, , ,

    I am trying to execute a simple query :
    SELECT A.Link,A.Start_Position,A.Stop_Position,A.Chromosome,A.Start,A.End FROM A,B where B.Start <= A.Start and B.End >= A.End;

    This query is taking more than two minutes to execute .. Can anyone please suggest me some changes ...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    would you kindly do a proper SHOW CREATE TABLE for your tables

    this will also reveal the indexes that you have declared
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well , I have not declared any indexes for tables :

    I just created a simple table as

    create table A (Link varchar(50),Feature varchar(50),Chromosome varchar(20),Start int(11) ,End int(11));
    and
    Create table B(Start int(11),End, int(11));

    Sorry I don't have a background of data base technologies ,
    Can you please tell what effect an index will have ?


    Thanks..

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ALTER tablea ADD INDEX start_end_ix_a ( start , end );
    ALTER tableb ADD INDEX start_end_ix_b ( start , end );

    then try your query again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I added these statements ... but query is still taking a long time more than 2 minutes ...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please do an EXPLAIN on the query and post the results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am trying to find column values (Chromosome,Linkage Group, start coordinates ,End coordinates ) from Table A,where start and end values (For table A) are in range of table B start and end values.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Code SQL:
    EXPLAIN SELECT A.Link,A.Start_Position,A.Stop_Position,A.Chromosome,A.START,A.END FROM A,B WHERE B.START <= A.START AND B.END >= A.END

    Run that and post the image. That is what is meant by post the explain.
    The only code I hate more than my own is everyone else's.

  9. #9
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have attached the result in screen shot :

    This is output I am getting :


    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE qtl ALL start_end_ix_qtl_positions 1205
    1 SIMPLE C ALL start_end_ix_ChromosomalPosition 562712 Range checked for each record (index map: 0x1)
    Attached Images Attached Images

  10. #10
    Non-Member
    Join Date
    Jun 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That error message has to do with the query to the database. Either the database "my_database" or the table in the database "abc" is none existant (spelling?) or there are no rows in the db for the fetch array to work.


Bookmarks

Posting Permissions

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