I m using inner join where there is no keys (Primary and foreign)
i want to know that Does key affects execution times?
i want to know that Does key affects execution times?
-
-
Hi, abhishektiwari,
Yes. If you have lots of information then it slows queries down by a lot. You need to find a balance and set keys on the columns you use most in SELECT but use as few keys as possible and try to avoid keys for columns which you UPDATE or INSERT often. I once managed to speed up a database access about 100 times by adding primary keys (the amount of joins in the queries was unbelievable).
Keys speed up SELECT a lot and slow down UPDATE/INSERT/DELETE.
Regards,
Tom -
Try to prepend the word "EXPLAIN" to your query and then run it from a mysql console window (or any application that allows you to type in the query and see the query results).
The EXPLAIN keyword will cause mysql to not carry out the query, but to provide you with a table containing information about what mysql needs to do in order to carry out the query. In particular, it will tell you whether ALL rows of a table need to be examined or only some of them due to the presence of an index.
This may help in giving you an idea of what indexes would help best. You could add the index, run the query again with EXPLAIN, see the results, and then remove the index and start all over again with another index.
If the EXPLAIN query shows you that too many tables need to examine ALL rows, then you definitely can speed things up by adding an index (or indexes).Comment
Comment