What is an index in SQL?

An index is used to speed up searching in the database. MySQL have some good documentation on the subject (which is relevant for other SQL servers as well): http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table to find exact matches. If you don’t have indexes on any column in the WHERE clause, the SQL server has to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

In MySQL you can use EXPLAIN in front of your SELECT statement to see if your query will make use of any index. This is a good start for troubleshooting performance problems. Read more here: http://dev.mysql.com/doc/refman/5.0/en/explain.html

Related Posts:

  1. How do composite indexes work?
  2. Python Pandas: Get index of rows which column matches certain value
  3. Python Pandas: Get index of rows which column matches certain value
  4. What datatype should be used for storing phone numbers in SQL Server 2005?
  5. Python, tuple indices must be integers, not tuple?
  6. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  7. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  8. What is SELF JOIN and when would you use it? [duplicate]
  9. How do I UPDATE from a SELECT in SQL Server?
  10. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  11. Online SQL Query Syntax Checker
  12. Must declare the scalar variable
  13. IDENTITY_INSERT is set to OFF – How to turn it ON?
  14. TypeError: tuple indices must be integers, not str
  15. Why does NULL = NULL evaluate to false in SQL server
  16. Difference between one-to-many and many-to-one relationship
  17. How do I format date and time on ssrs report?
  18. How to group by month from Date field using sql
  19. missing FROM-clause entry for table
  20. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  21. Sql query to insert datetime in SQL Server
  22. Postgres Error: More than one row returned by a subquery used as an expression
  23. SQL statement not working – “Operand type clash: date is incompatible with int’
  24. SQL Queries “00904. 00000 – “%s: invalid identifier”
  25. Comparing Dates in Oracle SQL
  26. How to divide two columns?
  27. MySQL starts with searching issue
  28. The used SELECT statements have a different number of columns
  29. using sql count in a case statement
  30. Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
  31. How can I group by date time column without taking time into consideration
  32. The provider did not return a ProviderManifestToken string Entity Framework
  33. Append table to an existing one: SQL Server
  34. How to order by column A and then by column B?
  35. SQL Server – An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’
  36. Conditional indexing with Numpy ndarray
  37. How to convert java.util.Date to java.sql.Date?
  38. “This SqlTransaction has completed; it is no longer usable.”… configuration error?
  39. WPDB Insert or if exists Update
  40. How to Use Wildcards in $wpdb Queries Using $wpdb->get_results & $wpdb->prepare?
  41. How to get the list of WooCommerce product image of a certain category from database?
  42. prepare() not working
  43. Extending WP_Query — Optimise SQL query
  44. How to filter sql only for a specific post type
  45. Add special meta box to custom post type
  46. Query between two meta values?
  47. How do I move a WordPress site to another server?
  48. Custom Fields Bulk Edit
  49. WordPress creating excessive joins on meta_query with search
  50. Filter WordPress posts by between parameter
  51. WPDB update row with != in where clause
  52. Using table lock with wp_insert_post?
  53. Enhanced WordPress Search
  54. How To Get Some Data From WordPress Database Using WordPress $wpdb Query?
  55. WordPress SQL Injections through User Agent
  56. $wpdb select date range of posts
  57. Retrieving a Value from a wp-database
  58. SQL query for custom taxonomy slugs
  59. How do I get the posts within a certain year/date(with sql query)?
  60. Query that loads a custom type AND posts whose ids are not inside those custom types custom fields
  61. Importing Geo data into wordpress database
  62. Setup private content for specific users with daily updates [closed]
  63. how to use $wpdb->prepare to update a custom table
  64. How to pass orderby params to $wpdb->prepare()?
  65. I need a SQL command to delete specific authors and all posts connected with them
  66. correct validate and sql query
  67. wpdb->get_row is selecting the variable as a column name
  68. problem with sql query
  69. How to get INSERT errors from $wpdb?
  70. how to insert missing tags into the posts through mySQL?
  71. Slow Query On Search
  72. Deactivation Hook does not remove database
  73. SQL query to set posts in bulk based on the post content
  74. WordPress SQL query to tag all posts containing a specific word on title
  75. Using AJAX to run SQL statement and populate dropdown
  76. Troubles about insering a POST with a SQL query, post_status
  77. Sql query does not work well
  78. JOIN and SUM different statement results (Mailster Database)
  79. Do I need to sanitize $_POST[‘keyword’] before send to ‘s’ parameter?
  80. Performing CRUD operations on front end in wordpress
  81. Display a number value from mysql query in WP
  82. Job and Employee Performance Tracking with Product Safety
  83. How to transfer categories (default) to custom taxonomy?
  84. Is there a way for two deferent themes to consume two different DB on a same WP instance?
  85. header background color changed after drop & import database in phpmyadmin
  86. blank page after update my theme
  87. Calculating efficiently on large amount of data generated by wp_query
  88. Include post_status check within $wpdb query
  89. SQL – JOIN last child
  90. How to order posts by the user_login that corresponds to a meta_value (which is a user id)?
  91. Special Query: Title, Terms, Content – %LIKE%
  92. SQL Query : how copy all tags of post into their post content in wordpress by sql query
  93. SQL command to export post_content from wp_posts using phpMyAdmin
  94. Adding profile data to database
  95. How to get the sum of each post with the same date
  96. Get count of rows based if column exists in two different tables
  97. How to dump a Microsoft SQL Server database to a SQL script?
  98. Postgresql: what does GRANT ALL PRIVILEGES ON DATABASE do?
  99. SQL query to retrieve the number of WordPress posts with all given categories
  100. select rows based on exact time (hours, minutes and seconds)

Leave a Comment

tech