By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,375 Members | 1,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,375 IT Pros & Developers. It's quick & easy.

What is Indexing in mysql and how it will be useful and how we can retrieve database content easily from a database table containing about 5lakhs datas

P: n/a
Hi All,

I need to select data from a database table containing huge amount of
data. Now I am storing data using one primary key and I am just using
simple select statement, and this process gives me the output but it is
taking long to execute the query. As much I had heared I want to use
some indexing or cluster indexing which might help me but I am not so
familiar with these things. So if any one having some solutions to
execute the query in short time period that may help me then please
welcome...

Thanks,
Amar :)

Jan 8 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Amar wrote:
Hi All,

I need to select data from a database table containing huge amount of
data. Now I am storing data using one primary key and I am just using
simple select statement, and this process gives me the output but it is
taking long to execute the query. As much I had heared I want to use
some indexing or cluster indexing which might help me but I am not so
familiar with these things. So if any one having some solutions to
execute the query in short time period that may help me then please
welcome...

Thanks,
Amar :)
Hi,

Indexing might well slow down things, depending on your situation.
A few rule-of-thumb-notes:
1) Indexing only speeds up SELECTS, and will slow down UPDATE and INSERT.
2) Only index columns that are used in the WHERE-clause.

For example: If you have a table with 10 columns, and one of them contains
the Primary Key (id), then indexing all others (x,y,z) will NOT help for:
SELECT id, x,y,z from tblMyTable WHERE (id=1234);

SO if you want to speed up your query, try to find out what it is spending
its time on, or make a lucky guess.

eg: If you have many queries like:
SELECT id,x,y,x FROM tblMyTable WHERE (x 200);
it might help to build an index on column x.

If you have many queries that use x and y, you might build an index on both
of them combined.

Profilertools might help too to find out what part of the query is the
bottleneck.

If you do not want to use a profiler, just benchmark how long your query
takes with microtime() before and after the query. (Or let mysql-exe tell
you how long it took.)

Whole books are written on the subject, so I leave it at this. :-)

Regards,
Erwin Moller

Jan 8 '07 #2

P: n/a
..oO(Erwin Moller)
>SO if you want to speed up your query, try to find out what it is spending
its time on, or make a lucky guess.
For "profiling" a query the EXPLAIN clause can be very helpful, even if
the result might be a bit difficult to understand:

EXPLAIN SELECT ... FROM ...

Micha
Jan 8 '07 #3

P: n/a
On 8 Jan 2007 08:25:43 -0800, "Amar" <gh**********@gmail.comwrote:
>I need to select data from a database table containing huge amount of
data. Now I am storing data using one primary key and I am just using
simple select statement, and this process gives me the output but it is
taking long to execute the query. As much I had heared I want to use
some indexing or cluster indexing which might help me but I am not so
familiar with these things. So if any one having some solutions to
execute the query in short time period that may help me then please
welcome...
Please ask in comp.databases.mysql where this is on-topic.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Jan 8 '07 #4

P: n/a
"Amar" <gh**********@gmail.comwrote in message
news:11**********************@42g2000cwt.googlegro ups.com...
Hi All,

I need to select data from a database table containing huge amount of
data. Now I am storing data using one primary key and I am just using
simple select statement, and this process gives me the output but it is
taking long to execute the query. As much I had heared I want to use
some indexing or cluster indexing which might help me but I am not so
familiar with these things. So if any one having some solutions to
execute the query in short time period that may help me then please
welcome...
Indexing means that a B-tree or similar structure is stored (the index) in
addition to the data. The index is a way to find specific values of a
specific field without searching every record in the database (assuming the
field type is ordered and there aren't a zillion duplicates).

In an approximate sense, the addition of the parallel data will change
SELECTs based on indexed data from O(N) to O(log N).

However, as Mr. Moller pointed out, it will make SELECTs faster, but will
slow down operations that involve adding or perhaps modifying records.

You can find a more lengthy explanation that might make sense here:

http://en.wikipedia.org/wiki/Btree

Dave.
Jan 8 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.