creating indexes on a table already containing data

Hi all,

I've got a database that has a few thousand rows, I've noticed that some
of the search queries (especially the large ones) are taking some time.
Im looking at adding indexes to my tables in order to speed up the data
retrieval.

My question is as follows: At this point in time if I add the indexes to
the various tables should I see immediate results in the query times or
do I have to wait for new information to enter the database (only new
data gets indexed?)
When does the data actually get indexed? Is it when its inserted or
continually when regards are inserted or updated in the database?

Thanks in advance.

--
------------------------------------------------------------------------
Angelo Zanetti
Systems developer
------------------------------------------------------------------------

*Telephone:* +27 (021) 469 1052
*Mobile:* +27 (0) 72 441 3355
*Fax:* +27 (0) 86 681 5885
*
Web:* http://www.zlogic.co.za
*E-Mail:* angelo@zlogic.co.za <mailto:angelo@zlogic.co.za>

Angelo, results should be (nearly) immediate. When you add an index,
MySQL creates an index for the existing data in your table. Later,
when data is added/updated/deleted, the index is updated
simultaneously.

With a few thousand rows, you should be able to get by adding a few
indexes where they seem to make sense. As your database grows, it
might be worth your while to go more in-depth with MySQL optimization.
The online manual has a good section at:
http://dev.mysql.com/doc/refman/5.0/...imization.html
and I also highly recommend Jeremy Zawodny's book "High Performance MySQL".

Dan


On 10/3/06, Angelo Zanetti <angelo@zlogic.co.za> wrote:
> Hi all,
>
> I've got a database that has a few thousand rows, I've noticed that some
> of the search queries (especially the large ones) are taking some time.
> Im looking at adding indexes to my tables in order to speed up the data
> retrieval.
>
> My question is as follows: At this point in time if I add the indexes to
> the various tables should I see immediate results in the query times or
> do I have to wait for new information to enter the database (only new
> data gets indexed?)
> When does the data actually get indexed? Is it when its inserted or
> continually when regards are inserted or updated in the database?
>
> Thanks in advance.
>
> --
> ------------------------------------------------------------------------
> Angelo Zanetti
> Systems developer
> ------------------------------------------------------------------------
>
> *Telephone:* +27 (021) 469 1052
> *Mobile:* +27 (0) 72 441 3355
> *Fax:* +27 (0) 86 681 5885
> *
> Web:* http://www.zlogic.co.za
> *E-Mail:* angelo@zlogic.co.za <mailto:angelo@zlogic.co.za>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

At 05:42 PM 10/3/2006, you wrote:


>Dan Nelson wrote:
>
>>In the last episode (Oct 03), Angelo Zanetti said:
>>
>>
>>>I've got a database that has a few thousand rows, I've noticed that
>>>some of the search queries (especially the large ones) are taking
>>>some time. Im looking at adding indexes to my tables in order to
>>>speed up the data retrieval.
>>>
>>>My question is as follows: At this point in time if I add the indexes
>>>to the various tables should I see immediate results in the query
>>>times or do I have to wait for new information to enter the database
>>>(only new data gets indexed?)
>>>
>>>When does the data actually get indexed? Is it when its inserted or
>>>continually when regards are inserted or updated in the database?
>>>
>>
>>When you add an index, Mysql builds a complete index of your table
>>immediately. For a few thousand rows it should take under a minute. The
>>index will automatically be updated as you insert rows or change
>>existing ones.
>>
>>
>
>thanks Dan and Dan.
>
>I will have a look at the mysql site. However I checked and there are 600
>rows approx in the database.
>The query im running has 3 subselect statements and takes quite a while,
>would you say that indexing wouldn't help as there arent that many rows in
>the database currently? And therefore consider rewriting parts of the
>statement?
>I will add the indexes as good measuse anyway.
>
>TIA

Use an "Explain" in front of your Select statement in the MySQL gui to see
which indexes it is using.

Example:

explain select * from table1 where cust_no=123 order by date_sold;

Mike

Dan Nelson wrote:

>In the last episode (Oct 03), Angelo Zanetti said:
>
>
>>I've got a database that has a few thousand rows, I've noticed that
>>some of the search queries (especially the large ones) are taking
>>some time. Im looking at adding indexes to my tables in order to
>>speed up the data retrieval.
>>
>>My question is as follows: At this point in time if I add the indexes
>>to the various tables should I see immediate results in the query
>>times or do I have to wait for new information to enter the database
>>(only new data gets indexed?)
>>
>>When does the data actually get indexed? Is it when its inserted or
>>continually when regards are inserted or updated in the database?
>>
>>
>
>When you add an index, Mysql builds a complete index of your table
>immediately. For a few thousand rows it should take under a minute.
>The index will automatically be updated as you insert rows or change
>existing ones.
>
>
>

thanks Dan and Dan.

I will have a look at the mysql site. However I checked and there are
600 rows approx in the database.
The query im running has 3 subselect statements and takes quite a while,
would you say that indexing wouldn't help as there arent that many rows
in the database currently? And therefore consider rewriting parts of the
statement?
I will add the indexes as good measuse anyway.

TIA

--
------------------------------------------------------------------------
Angelo Zanetti
Systems developer
------------------------------------------------------------------------

*Telephone:* +27 (021) 469 1052
*Mobile:* +27 (0) 72 441 3355
*Fax:* +27 (0) 86 681 5885
*
Web:* http://www.zlogic.co.za
*E-Mail:* angelo@zlogic.co.za <mailto:angelo@zlogic.co.za>