Timeline for Profiling a SELECT statement with an index
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 4, 2021 at 10:34 | vote | accept | Johnny | ||
| Jul 1, 2021 at 16:18 | history | edited | Johnny | CC BY-SA 4.0 |
Adding info from SHOW SESSION STATUS LIKE 'Handler%'; as per @RickJames suggestion
|
| Jul 1, 2021 at 7:12 | comment | added | Johnny |
@RickJames SELECT @@innodb_buffer_pool_size/1024/1024/1024; shows 0.0078125. Matching rows were scattered in both cases, although I suppose in the 1M case it's possible they were less scattered. Is there a way for me to find out?
|
|
| Jun 30, 2021 at 16:51 | answer | added | Rick James | timeline score: 3 | |
| Jun 30, 2021 at 16:36 | comment | added | Rick James |
What is the value of innodb_buffer_pool_size? Were the matching rows scattered around? Or clumped together?
|
|
| Jun 30, 2021 at 12:49 | comment | added | Rory | In SQL Server you can ask for statistics on logical reads, query plan, etc to get more info about what's actually going on. That's much better than guessing and timing with the clock, e.g. since server might be doing lots of other things, memory load & caching is different, etc. Not sure how that works for MySql though... | |
| Jun 30, 2021 at 12:47 | comment | added | Rory | Also the index size in the 10M scenario means it's a bit bigger and hence a bit slower, but I'd guess that doesn't make much difference. | |
| Jun 30, 2021 at 12:46 | comment | added | Rory | Sorry, I was being a bit lazy explaining what I meant. DBs usually read from disc in pages. The index entries identify the desired pages & location within the page. Each page will hold many rows but the more data you have the fewer rows of interest will be on each page, depending on the distribution of that data.. If all your 100 rows are next to each other in both 1M and 10M scenarios then disc access is probably approx the same. If your rows are evenly distributed amongst the other data then in the 10M scenario you'll need to read more pages. | |
| Jun 30, 2021 at 12:37 | comment | added | Johnny | I don't understand, why is there more disk access for 10M rows? I thought the index points to the relevant row and then that's read into memory. | |
| Jun 30, 2021 at 12:25 | comment | added | Rory |
I'm no expert in mysql but some thoughts: a) your timings are quite small so it's hard to conclude too much, b) assuming that's not a clustered key then MySql still has to do key lookups to get all the other columns, meaning there's more disc access in the 10M row scenario. If you change your query to select first from ... you're likely to see less time increase as it can take the data directly from the index. I'd expect the extra time for the select * ... would not increase very much with more rows in the table.
|
|
| Jun 30, 2021 at 11:41 | history | asked | Johnny | CC BY-SA 4.0 |