How to speed up sql queries ? Indexes?

by

Indexes are essential to any database.

Speaking in “layman” terms, indexes are… well, precisely that. You can think of an index as a second, hidden, table that stores two things: The sorted data and a pointer to its position in the table.

Some thumb rules on creating indexes:

  1. Create indexes on every field that is (or will be) used in joins.
  2. Create indexes on every field on which you want to perform frequent where conditions.
  3. Avoid creating indexes on everything. Create index on the relevant fields of every table, and use relations to retrieve the desired data.
  4. Avoid creating indexes on double fields, unless it is absolutely necessary.
  5. Avoid creating indexes on varchar fields, unless it is absolutely necesary.

I recommend you to read this: http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

More Related Contents:

  1. Storing JSON in database vs. having a new column for each key
  2. Which DB technology is sued to solve the following search issue? [closed]
  3. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  4. How to select the nth row in a SQL database table?
  5. 1052: Column ‘id’ in field list is ambiguous
  6. How to do a batch insert in MySQL
  7. Count the occurrences of DISTINCT values
  8. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  9. How do I restore a dump file from mysqldump?
  10. Cast int to varchar
  11. Import SQL file into mysql
  12. How to change the default port of mysql from 3306 to 3360
  13. Does mysql have the equivalent of Oracle’s “analytic functions”?
  14. How to skip certain database tables with mysqldump?
  15. “where 1=1” statement [duplicate]
  16. MySql views performance [closed]
  17. How to set a MySQL row to READ-ONLY?
  18. MySQL: LAST_INSERT_ID() returns 0
  19. Using reserved words in column names
  20. Calculating a Moving Average MySQL?
  21. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  22. How to insert a file in MySQL database?
  23. Strings as Primary Keys in MYSQL Database [closed]
  24. MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
  25. What is the difference between SQL and MySQL? [closed]
  26. Selecting distinct 2 columns combination in mysql
  27. How to store a one to many relation in MySQL database?
  28. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;
  29. MySQL: Computed Column
  30. Optimistic locking in MySQL

Leave a Comment