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]
