List:General Discussion« Previous MessageNext Message »
From:mos Date:October 4 2006 12:51am
Subject:Re: creating indexes on a table already containing data
View as plain text  
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 
Thread
creating indexes on a table already containing dataAngelo Zanetti3 Oct
  • Re: creating indexes on a table already containing dataDan Buettner3 Oct
  • Re: creating indexes on a table already containing dataDan Nelson3 Oct
    • Re: creating indexes on a table already containing dataAngelo Zanetti4 Oct
      • Re: creating indexes on a table already containing datamos4 Oct