database - how to do right indexing for fast execution of large data in mysql

Question

I have a table which has a huge amount of data. I have 9 column in that table (bp_detail) and 1 column of ID which is my primary key in the table. So I am fetching data using query

select * from bp_detail

so what I need to do to get data in a fast way? should I need to make indexes? if yes then on which column?

I am also using that table (bp_detail) for inner join with a table (extras) to get record on the base of where clause, and the query that I am using is:

select * from bp_detail bp inner join extras e
on (bp.id = e.bp_id)
where bp.id = '4' or bp.name = 'john'

I have joined these tables by applying foreign key on bp_detail id and extras bp_id so in this case what should I do to get speedy data. Right Now I have an indexed on column "name" in extras table.

Guidance highly obliged

For your first query, indexes will do nothing. For your second, it seems very odd to search by 'id or name' — Strawberry, Jan 19 '18 at 08:17
See [_indexing Cookbook_](http://mysql.rjweb.org/doc.php/index_cookbook_mysql) . — Rick James, Jan 19 '18 at 16:39

score 0 · Answer 1 · answered Jan 19 '18 at 07:50

0

depending on your searching criteria, if you are just selecting all of the data then the primary key is enough, to enhance the join part you can create an index on e.bp_id can help you more if you shared the tables schema

answered Jan 19 '18 at 07:50

Diab

142
7

The primary key is irrelevant when selecting all data – Strawberry Jan 19 '18 at 08:21

Mike Doe · Accepted Answer · 2018-01-19T12:42:55.057

If selecting all records you would gain nothing by indexing any column. Index makes filtering/ordering by the database engine quicker. Imagine large book with 20000 pages. Having index on first page with chapter names and page numbers you can quickly navigate through the book. Same applies to the database since it is nothing more than a collection of records kept one after another.

You are planning to join tables though. The filtering takes place when JOINING:

on (bp.id = e.bp_id)

and in the WHERE:

where bp.id = '4' or bp.name = 'john'

(Anyway, any reason why you are filtering by both the ID and the NAME? ID should be unique enough).

Usually table ID's should be primary keys so joining is covered. If you plan to filter by the name frequently, consider adding an index there too. You ought to check how does database indexes work as well.

Regarding the name index, the lookup speed depends on search type. If you plan to use the = equality search it will be very quick. It will be quite quick with right wildcard too (eg. name = 'john%'), but quite slow with the wildcard on both sides (eg. name = '%john%').

Anyway, is your database large enough? Without much data and if your application is not read-intensive this feels like beginner's mistake called premature optimization.

for the name search I make an index on name column. Is it good enough for fast search? — Haris Khan, Jan 19 '18 at 07:58

database - how to do right indexing for fast execution of large data in mysql

2 Answers2