-1

Making my concept more clear about indexes in Mysql. What i know is indexes are used to make your query faster. except that i have couple of questions to know about.

Let's say i'm having a query: 

SELECT books.name, books.name2, books.id, books.image, books.faith, books.topic, books.downloaded, books.viewed, books.language, books.size, books.author as author_id, authors.name as author_name, authors.aid from books LEFT JOIN authors ON books.author = authors.aid WHERE books.id = '".$id."' AND status = 1
  1. Is any index applicable for this select query while it has JOIN?
  2. After making an index on a column query for that will be optimized or changed ?
  3. How to make index for this query and against which column?
  4. What's the other benefits or disadvantages of using indexes?
  5. On which case indexes should be avoided and where should use indexes for more?
  6. Do indexes applicable on random queries ?
  7. Are indexes more efficient on IDS ?

Please apprise, thank you in advanced !

RanaHaroon
  • 445
  • 6
  • 20
  • Please go through [link1](http://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practices), [link2](http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work) – Sumit Pandey Oct 16 '15 at 04:20
  • [Cookbook on building an index](http://mysql.rjweb.org/doc.php/index_cookbook_mysql). – Rick James Nov 01 '15 at 04:21
  • Does this answer your question? [MySQL indexes - what are the best practices?](https://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practices) – philipxy Dec 21 '19 at 05:47

1 Answers1

1

You can check details on below links.

https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

http://mydbsolutions.in/query-optimization-2/

Your queries are here-

  1. Is any index applicable for this select query while it has JOIN?

It will depend on various factors like if your table have very less data or approx. 70% data is same in index column then mysql will prefer to scan table instead of index. In simple all your join columns should be indexed (will be indexed if you use foreign key concept other wise you should indexed them). Also on which column your query is filtering most data that field should be indexed. In your case you are filtering data on books.id which should be primary key so already indexed.

  1. After making an index on a column query for that will be optimized or changed?

It will be automatically start to use index but in some cases may be you need to change your query. Suppose you are using a filter condition as "date(order_date)='2015-10-15'", even after creating index on order_date it will not be used so you have to change your query as "order_date>='2015-10-15 00:00:00' and order_date<='2015-10-15 23:59:59'" if you order_date column data type is datetime or timestamp.

  1. How to make index for this query and against which column?

Here I am not seeing any need of making index as your condition is on books table primary key and it will be already indexed.

  1. What's the other benefits or disadvantages of using indexes?

If you create index blindly then at the time of record insertion/updation etc each time index will be updated and will slow the process. Even heavy index will perform slow. Also will consume more disk space.

  1. On which case indexes should be avoided and where should use indexes for more?

If more than 70% data is same for any column then no need to create index on them like status or is_deleted type columns as mostly data will be active.

  1. Do indexes applicable on random queries ?

Yes index work on random queries, for repeatable queries you can use query cache which will be more efficient.

  1. Are indexes more efficient on IDS ?

Yes.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30