Is there any way to check the performance of mysql Indexing

by

Following query will tell you whether query uses index or not:

EXPLAIN EXTENDED SELECT col1, col2, col3, COUNT(1) 
FROM table_name 
WHERE col1 = val 
GROUP BY col1 
ORDER BY col2;

SHOW WARNINGS;

You can add covering index for best performance.

For covering index you add columns used in where clauses first then columns used in group by the columns used in order by and then columns used in select.

e.g. for above query you can add covering index KEY(col1, col2, col3)

*Note Adding more indexes will slow down your insert queries.

More Related Contents:

  1. Laravel one to many relation with a one to one relation
  2. Gather column name of a table from database [closed]
  3. I am seeing this error on submitting form Error: query was empty [closed]
  4. How to convert the json image url string into Bitmap image to display images in android application
  5. save user images in MYSQL database by which method? [closed]
  6. How to use multiple databases in Laravel
  7. Many-to-many relationships examples
  8. PDO get the last ID inserted
  9. Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  10. Show values from a MySQL database table inside a HTML table on a webpage [duplicate]
  11. Warning: mysqli_connect(): (HY000/1045): Access denied for user ‘username’@’localhost’ (using password: YES)
  12. How can I store and retrieve images from a MySQL database using PHP?
  13. How to upload images into MySQL database using PHP code
  14. Importing CSV data using PHP/MySQL
  15. How to store file name in database, with other info while uploading image to server using PHP?
  16. Mechanisms for tracking DB schema changes [closed]
  17. PDO::PARAM for type decimal?
  18. How to store an array into mysql?
  19. Selecting a float in MySQL
  20. Get all child, grandchild etc nodes under parent using php with mysql query results
  21. phpmyadmin automatic logout time
  22. How to execute raw queries with Laravel 5.1?
  23. Saving images in database mysql
  24. MySQL skipping first row
  25. PHP – Secure member-only pages with a login system
  26. Fatal error: Call to undefined function mysqli_result()
  27. Laravel nested relationships
  28. mysql injection damages?
  29. How do I save PHP session data to a database instead of in the file system?
  30. Mysqli multiple row insert, simple multi insert query [duplicate]

Leave a Comment