Memory resident table is taking much time compared to disk resident table.......

I have created this table ....
create table  Rules_horizontal (
         Fragment_name varchar(20) not null,
         Attribute_name varchar(20) not null,
                         |              |               |
                         |              |               |
         primary key(Fragment_name,Attribute_name),
         index (Fragment_name)
) ;

and the below code snippet is taking 0.586 secs for execution
-----------------------------Begin Snippet------------------------------------------
 Line:12        while ((row1 = mysql_fetch_row(pResult)) != NULL)  {
 Line:13                    temp = row1[0];
 Line:14                   sqlQuery="select * from Rules_horizontal where Fragment_name='"+temp+"'";
 Line:15                  qResult=get_result(sqlQuery.c_str());
                        }
------------------------End Snippet----------------------------------------------------
but when I kept the table in memory.....
create table  Rules_horizontal (
         Fragment_name varchar(20) not null,
                         |              |               |
         primary key(Fragment_name,Attribute_name),
         index (Fragment_name)
)  Type = Heap;

the above code snippet is taking 5min .456 secs ..............Line14 is taking a long time for exection.......
Memory resident Table shud take less time than disk resident table but i am getting the opposite...............
Am I doing any mistake??? ........... Plz help.

terminator_4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
How many rows in your table?
If there are too many to keep the entire table in memory, then the table is "swapped" from disk (swap) to memory before your query can go on...  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terminator_4Author Commented:
Number of Rows: 6000
RAM: 256 MB
OS: FC2
mysql  Ver 11.18 Distrib 3.23.58, for redhat-linux-gnu (i386)
NovaDenizenCommented:
Memory tables use hash indexes by default.  Maybe your second query depends on the ordered-index properties of B-Trees to be efficient (I don't quite see how...)

Try using these to create your primary key and index:

PRIMARY KEY USING BTREE (Fragment_Name, Attribute_Name),
index USING BTREE (Fragment_Name)

I think this requires mysql version later than 4.0.1.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.