1000 FAQs, 500 tutorials and explanatory videos. Here, there are only solutions!
MySQL: Optimising queries
The number and size of databases is unlimited, but you must optimise your queries and tables (with indexes) on which your databases are located, especially when the size of the database increases considerably.
Optimising queries
Indexes are used to find result lines with a specific value, very fast. Without indexes, MySQL has to read all the lines in succession and make the necessary comparisons to extract a relevant result every time. The bigger the table, the more resources it requires. If the table has an index for the columns used, MySQL can then find the positions of the lines quickly in the data file, without having to run through the entire table.
The EXPLAIN command allows an SQL query to be audited, thus identifying the number of rows read to execute the query. If the number is very high, using an index can prove useful in order to optimise the query. Adding an index is usually done on columns used in the WHERE clause of an SQL query.
The syntax for adding indexes is as follows:
ALTER TABLE tablename ADD INDEX indexname (column1, column2, ...);
Find out more/References
http://dev.mysql.com/doc/refman/5.0/fr/mysql-indexes.htmlhttp://dev.mysql.com/doc/refman/5.0/fr/explain.html