Slow Query

Databases
1

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

would you kindly do a proper SHOW CREATE TABLE for your tables

this will also reveal the indexes that you have declared

3

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

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

5

I added these statements … but query is still taking a long time more than 2 minutes …

6

please do an EXPLAIN on the query and post the results

7

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 

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.

9

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)

10

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.