0

I need to find all indexes available on a query.

I have a datatable, with filters on top of each column, but I want to enable filters only for indexed columns.

For this I have a main query to which when a value in any filter is added a clause is added.

I need to find all the indexed columns available on this main query.

I will use this info to only show column filters on indexed columns.

Main query looks like this, there book.title and author.name are indexed columns in there respective table and pages and genre are not, thus column filter will only appear on book.title and author.name.

SELECT book.title, book.pages, author.name, author.genre
FROM books_table as book
         JOIN author_table as author ON author.id = books.author_id;

I need to figure these indexes dynamically. I have many many queries like this and do not want to do it manually, doing it manually also will need changes in code when indexes are are added or removed.

if it matters, my database is Mysql 5.6

Thanks

karmendra
  • 2,206
  • 8
  • 31
  • 49
  • Run an EXPLAIN on your query? – TJ- Jun 24 '20 at 18:56
  • I do not think it will give me all the available indexes in the query, it gives me only the index used for running the current query. – karmendra Jun 25 '20 at 09:44
  • Indices are not on queries, they are defined on columns of the table. EXPLAIN gives the possible indices that the query optimizer may use to run the query. Do you want the columns on which indices are present in the table? – TJ- Jun 25 '20 at 10:01
  • In your (type of) query, the use of an indexed column will in most cases eliminate the use of indexes on other tables' columns, as they are bound by the join. E.g. you usually cannot use book.title and author.name in the same query, as if you use book.title, mysql will have to use an index on author.id to do the join. In other words: you probably could add filters to all other columns without any difference in performance if at least one indexed column is among them. On the other hand, NOT having filters on some columns may reduce usuability of your app. – Solarflare Jun 25 '20 at 11:00
  • This of course is being said while unaware of your app or other restrictions, so you may be ok with this. But you may consider just adding all filters, and then log/check/guess/ask which are used most, and then add an index to those. Also be aware that an index on title will only be useful to find the beginning of the string (which may or may not be what you want to do). So you may be overthinking your problem/idea, e.g. you may not actually need to implement what you are planning to do here. – Solarflare Jun 25 '20 at 11:01
  • @TJ- Yes I need columns on which indices are present – karmendra Jun 25 '20 at 11:05
  • To know which columns are indexes, you can use e.g. [How to see indexes for a database or table in MySQL?](https://stackoverflow.com/a/55369706) (but as I said, you can usually only use one of those at the same time). – Solarflare Jun 25 '20 at 11:10
  • @Solarflare, So currently my app has filter on all columns, problem is when user filters by a column that is not indexed it takes a lot of time to retrieve data because there are millions of rows. I will check that link. – karmendra Jun 25 '20 at 11:12
  • So I will have to figure all the tables in a query and find the defined indices on each of them. – karmendra Jun 25 '20 at 11:13
  • Well, usually, my approach wouldn't be to reduce the usuability of my app because some actions are slow, but to add indexes to speed up those slow actions. As if your users experience slow filters, they are obviously currently using/needing(?) those filters. But yes, to know which columns are indexed, you need to know which tables you use. But again: you can (usually) only use one index in your type of query. So if you would e.g. index the 10 most important columns (so that most searches have one of those in it), you could keep all filters/usuability and still get the same speed. – Solarflare Jun 25 '20 at 11:57
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/216642/discussion-between-karmendra-and-solarflare). – karmendra Jun 25 '20 at 14:34

0 Answers0