|
|
Posted by Erwin Moller on 01/08/07 16:42
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
Navigation:
[Reply to this message]
|