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]