472,796 Members | 2,324 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,796 software developers and data experts.

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

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
4 1997
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
..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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: James | last post by:
HI, I'm looking for a script that will allow users/admins to have a one click backup solution for a MYSQL Database.. 'BACK DATABASE' button, click and its done... The a restore option, that...
3
by: NotGiven | last post by:
I am researching the best place to put pictures. I have heard form both sides and I'd like to know why one is better than the other. Many thanks!
125
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from...
2
by: Sandman | last post by:
Just looking for suggestion on how to do this in my Web application. The goal is to keep track of what a user has and hasn't read and present him or her with new material I am currently doing...
23
by: JDeats | last post by:
Just spent some time browsing around here: http://msdn.microsoft.com/Longhorn/ I can see the benefits from WinFS (as long as we tag all in-coming data this should be nice, tagging everything...
0
by: Jose Angel Campos | last post by:
Hi, I will like to know if you know how to do it. I have this code for read google news and I want to added to my DB MYSQL so I can store the news for reading went I want. Here is the code I use...
3
by: JM | last post by:
Before storing information from a form in database I perform follwing operations on it : $path = mysql_real_escape_string(strip_tags(trim(urldecode($_POST)))); $summary =...
0
Coldfire
by: Coldfire | last post by:
Since i cannot show the differences in a two-column like table. I am first putting MS SQL Server 2005 and then MySQL 5.x. MS SQL Server 2005 Brief Overview - SQL Server is a full-fledged...
6
by: fpcreator2000 | last post by:
Hello everyone. I'm having problems with a page I've created that is used to insert data into a database. It uploads two files into two distinct folder, and it takes the filenames and inserts...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.