Hi Ken,
I added the index as per your suggestion ,but looks
like mysql is not using that
index(user_id,user_data2).

Here is my actual explain output for real tables.

NOTE For alert_type_list :type_id_idx is index on
alr_type,id

id_type_idx is index on id,alr_type

id_idx  is index on id

id on nfk_key_list is PRIMARY KEY

1) BEFORE CREATING index id_type_idx 

explain  select
nfk_key_list.id,nfk_key_list.nfk_string from
nfk_key_list,alert_type_list where
alert_type_list.alr_type ="E/U"  AND
nfk_key_list.id=alert_type_
list.id;
+-----------------+--------+----------------------+--------------+---------+---------------------+--------+-------------+
| table           | type   | possible_keys        |
key          | key_len | ref                 | rows  
| Extra       |
+-----------------+--------+----------------------+--------------+---------+---------------------+--------+-------------+
| alert_type_list | ref    | type_id_idx,id_idx   |
type_id_idx  |      50 | const               | 118271
| Using where |
| nfk_key_list    | eq_ref | PRIMARY              |
PRIMARY      |     255 | alert_type_list.id  |      1
|             |
+-----------------+--------+----------------------+--------------+---------+---------------------+--------+-------------+

2:AFTER CREATING id_type_idx

mysql> explain select
nfk_key_list.id,nfk_key_list.nfk_string from
nfk_key_list,alert_type_list where
alert_type_list.alr_type ="E/U"  AND
nfk_key_list.id=alert
_type_list.id;
+-----------------+--------+-----------------------------------+--------------+---------+---------------------+--------+-------------+
| table           | type   | possible_keys            
        | key          | key_len | ref                
| rows   | Extra       |
+-----------------+--------+-----------------------------------+--------------+---------+---------------------+--------+-------------+
| alert_type_list | ref    |
type_id_idx,id_idx,id_type_idx    | type_id_idx  |    
 50 | const               | 127487 | Using where |
| nfk_key_list    | eq_ref | PRIMARY                  
        | PRIMARY      |     255 | alert_type_list.id 
|      1 |             |
+-----------------+--------+-----------------------------------+--------------+---------+---------------------+--------+-------------+

Looks like even after creating the new index it is not
using it.
No improvement to query
It is taking about 50-55 mins to get data for about
200K matches for above query.
nfk_key_list has about 13Million and alert_type_list
has about 12 million rows.

Thanks



--- Ken Menzel <[EMAIL PROTECTED]> wrote:
> Hi Kamlesh,
>    You should send an explain of the query,  but if
> there is no index
> on tableB.user_id your join will not work well,
> since the actual join
> would be on tableB.user_id=tableA.user_id
> 
> Either change your index on table b to be
> user_id,user_data2 or add
> this index.
> 
> Hope this helps,
> Ken
> Ken
> ----- Original Message ----- 
> From: "kamlesh pandey" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, April 21, 2004 5:10 PM
> Subject: mysql using big two tables in BSD
> 
> 
> > Hi,
> >   I'm new to mysql.
> >   I have two big tables ,tableA>4GB and
> tableB>1GB.
> >   both tables around 10 million rows,each.
> >
> >   tableA has following two cols.
> >
> >   user_id(varchar(255) PRIMARY KEY
> >   user_data1(MEDIUMTEXT)
> >   entries looks like
> >
> >  user1 xyz................
> >  user2 x1..................
> >
> >  tableB has following cols
> >
> >  user_data2:varchar(50)
> >  user_id(varchar(255)).
> >
> > tableB is indexed on (user_data2,user_id) and on
> > user_id.
> > there is no primary key in tableB since,it can
> have
> > entries like
> > A   user1
> > B   user1
> > A   user2
> > C   user2.....
> >
> > I NEED to SELECT data from both tables as
> follwoing
> >
> > SELECT tableA.userid,tableA.user_data1 from
> > tabelA,tableB where tableB.user_data2="myinput"
> AND
> > tableB.user_id=tableA.user_id
> >
> > It was good while the table size was small,but
> since
> > the table size is big and growing,the query is
> > becoming slow.
> > I'm using  mysql_use_result().
> > to get the result.
> >
> > Any suggestion either on client query or server
> tuning
> > will
> > be helpful.
> >
> > thanks
> >
> >
> >
> > =====
> > "Don't worry about the world coming to an end
> today. It's already
> tomorrow in Australia."
> >
> > ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º°
> > Do You Yahoo !
> > ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º°
> >
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Photos: High-quality 4x6 digital prints for
> 25¢
> > http://photos.yahoo.com/ph/print_splash
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 


=====
"Don't worry about the world coming to an end today. It's already tomorrow in 
Australia."

¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º°
Do You Yahoo !
¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º°


        
                
__________________________________
Do you Yahoo!?
Yahoo! Photos: High-quality 4x6 digital prints for 25¢
http://photos.yahoo.com/ph/print_splash

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to