MySQL Forums
Forum List  »  InnoDB

Re: optimized searching - need desperate help
Posted by: Jay Pipes
Date: July 02, 2005 07:10PM

Ankur Saxena wrote:
> Hi Jay,
>
> Thank you so much, it did the trick. The indexing
> was the one which solved the problem. I am not
> very familiar with indexing, but how do I decide
> to make index or not. This is really helpful,
> infact the query was responding in 0.00 sec, which
> is phenominal, now I have 1.1 million records and
> still the response, even if it is in the .1 sec
> range I am happy.

excellent. I'm glad things worked out.

>
> Again how do I find more about optimizing my
> Database designs for the type of queries I
> perform. Like in this case you mentioned the
> index, can you suggest some place where I can do a
> quick read.
>

Well, there are a number of resources available, both in the online MySQL reference manual and on the Internet. You can just do some preliminary research to understand the basics of indexing, and then return to the MySQL documentation for stuff specific to MySQL's implementation and specifics.

When you have to decide which columns to put an index on, first look to see what your queries are using in the WHERE, ON, and GROUP BY expressions. An index will help MySQL quickly and effectively search, filter, and in some cases group, data keys.

In addition, you can pick up a book on beginning or intermediate SQL which covers how indexes are utilized.

> Also I dont need to recreate indexes right, once
> done any amount of data I put in will always be
> indexed. Also how does it really help to index and
> what are the negatives of index?
>

No, generally, you will not need to recreate indexes, although sometimes, with lots of update activity, and especially after a DELETE of a large number of records, you may want to issue an ANALYZE TABLE, which will rebuild the statistics about indexes which MySQL uses to determine the relative utility of the index...

The negatives of index usage is that they can slow INSERT and UPDATE queries because not only do the data records need updated, but also the index records as well. This performance impact will not be very noticeable, however, unless you a) have a very large number of indexes on a table and/or b) you have a *very* high write activity in your database (at least 15-25% of your queries are UPDATE/INSERT/DELETE queries)

> Thanks
> Ankur

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: optimized searching - need desperate help
2160
July 02, 2005 07:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.