{"id":2679,"date":"2010-05-11T15:31:20","date_gmt":"2010-05-11T13:31:20","guid":{"rendered":"http:\/\/localhost\/helpcentre\/?p=2679"},"modified":"2022-11-15T13:26:30","modified_gmt":"2022-11-15T11:26:30","slug":"index-database-optimization","status":"publish","type":"post","link":"https:\/\/xneelo.co.za\/help-centre\/website\/index-database-optimization\/","title":{"rendered":"Database Indexes for database optimization"},"content":{"rendered":"

If you are looking into troubleshooting the performance of a database<\/strong> and the queries that run on it, you can make use of the database feature, database indexes<\/strong>. This feature if used efficiently, by ensuring indexes are configured and being used correctly in your queries, may help in improving query response times and avoid any unnecessary use of more expensive newer hardware.<\/p>\n

Database table example:<\/b><\/p>\n

Table name: customers<\/u><\/p>\n\n\n\n\n\n\n\n
Name<\/td>\nType<\/td>\nSize<\/td>\nDescription<\/td>\n<\/tr>\n
customer_id<\/td>\nchar<\/td>\n10<\/td>\nNOT NULL<\/td>\n<\/tr>\n
name<\/td>\nvarchar<\/td>\n40<\/td>\n<\/td>\n<\/tr>\n
surname<\/td>\nvarchar<\/td>\n40<\/td>\n<\/td>\n<\/tr>\n
phone_number<\/td>\nint<\/td>\n11<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

To find the customer Dave\u2019s phone number with the customer_id = \u2018274178\u2019, you would need to:<\/p>\n

\n
    \n
  1. Run a SQL query<\/li>\n
  2. SELECT phone_number FROM customers WHERE customer_id = \u2018274178\u2019<\/li>\n
  3. To find the correct record MySQL would have to search through all the records in the entire table i.e. possibly searching through thousands of entries.<\/li>\n<\/ol>\n<\/div>\n

    Database Indexes <\/strong>work in the same way as a book index. The index helps the reader find the relevant information quickly by searching the index i.e. instead of going through each page of the book until the correct information is found. Tables may have multiple indexes applied to them. Each index may reference one or more columns. The best way to see where indexes will provide benefit is to see which columns are referenced in the \u201cWHERE\u201d portion of the query.<\/p>\n

    Explain command:<\/b><\/p>\n

    EXPLAIN is a command that provides some insight into the methods MySQL will use to optimise a query and its output can be a useful tool to identify and remove obstacles. Using EXPLAIN is as simple as adding the keyword EXPLAIN at the start of a query.<\/p>\n

    Example:<\/b> The following will demonstrate the \u2018explain command\u2019 by investigating the table above:<\/p>\n

    EXPLAIN SELECT customer_id,name,surname FROM customers WHERE customer_id = \u2018274178\u2019;<\/p>\n\n\n\n\n
    table<\/td>\ntype<\/td>\npossible_keys<\/td>\nkey<\/td>\nkey_len<\/td>\nref<\/td>\nrows<\/td>\nExtra<\/td>\n<\/tr>\n
    CUSTOMERS<\/td>\nALL<\/td>\nNULL<\/td>\nNULL<\/td>\nNULL<\/td>\n283211<\/td>\n2<\/td>\nwhere used<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Looking at the possible_keys<\/b> the output is null<\/b>, which indicates there are no Indexes currently and that MySQL has to search 283211 records to return the result.<\/p>\n

    Create index example:<\/b><\/p>\n

    CREATE INDEX indx_customer_id ON customer (customer_id);<\/p>\n

    If we re-run the EXPLAIN the output changes to:<\/p>\n\n\n\n\n
    table<\/td>\ntype<\/td>\npossible_keys<\/td>\nkey<\/td>\nkey_len<\/td>\nref<\/td>\nrows<\/td>\nExtra<\/td>\n<\/tr>\n
    customers<\/td>\nconst<\/td>\nPRIMARY<\/td>\nPRIMARY<\/td>\n10<\/td>\nconst<\/td>\n1<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Comparing the output to the previous explain output, you\u2019ll notice that the possible_keys<\/b> field now says PRIMARY<\/b> and only had to search 1 row to find the record.<\/p>\n

    Note:<\/b> Indices can be on any column on the table<\/p>\n

    For additional information about database indexing and database optimizing refer to the following:<\/p>\n