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
