Speed up MySQL 25-100x with Indexing

Показать описание

Today we will take a quick look at how to speed up a query on a database table with over 850,000 rows and see how indexing can take a query from minutes to seconds.

Рекомендации по теме

Комментарии

Многие проекты в наши дни очень хороши и неформальны для всех, спасибо, что организуете их для нас

Amazongadgets

You've explained so well so please keep on coming like this video, It will help lot of people to save so much money on server costing.

mayurchavhan

You can tune the query with something like "SELECT id, SUM(price) AS INCOME FROM sales GROUP BY item, cashier" and add some "WHERE" conditions to filter just the items and/or cashier which you want (i.e WHERE cashier=5).

What you are facing is a N+1 problem. If the query is processed in an asynchronous way that cannot be a problem but in your case is synchronous so you have to focus on fixing the N+1 and you can either optimize the query as I suggested or create a projection. 11s normally isn't acceptable for loading a webpage.

Anyway a 4x gain in performance with just indexes is a good example of how powerful they can be.

miguelbel

Additionally I think the query would speed up immensely if you changed the loop from querying a thousand times into into building a single query that has a range in it. Queries in loops are a big no go in general

bowiemtl

Thank you so very much! Saved me a lot of time.

ElTrabajo

Don't run queries inside loop, instant performance boost. Bonus: use composite index(cashier, item), super instant performance boost.

BsiennKhan

Is it bad if I index 7-8 out of 24 columns in the table? From which factor it will affect the database which have more than 300k rows of data in table? Can you please help me with it? Thanks in advance

theamanjs