Ok. Just double check that every column used in a join is indexed and preferably the same exact datatype as the corresponding column in the other table.
1 Like
Ok, I will check , thanks, I will let you know what happened
1 Like
So i recreate this better just different names from the beginning:
I can change the deault innodb buffer pool size as @jpark said, I just need to make sure of other thins before modify. But I created a copy of the database and I will change the size and let´s see how this work.
-------------------------------------- QUERY ---------------------------------------------
select pc.fechaHora_ped as 'Fecha',pr.col1 as 'col1', pr.col2 as 'col2', pc.d2 as 'd2', pc.d10 as 'd10',
IF(pl.detail= 0, 'Great',
IF(pl.detail= 2,'Error 1',
IF(pl.detaile= 4,'Error 2 ',
IF(pl.detaile= 16,'Error 3',
IF(pl.detaile= 20,'Error 4',
IF(pl.detaile= 30,'Error 5',
IF(pl.detaile= 40,'Error 6',
IF(pl.detaile= 50,'Error 7',
IF(pl.detaile= 60,'Error 8',
IF(pl.detaile= 70,'Error 8',
IF(pl.detaile= 80,'Error 9',pl.detail)))))))))))
AS "Detail"
FROM tablesubgeneral pl JOIN tableGeneral pc USING (d2) JOIN tablePr pr ON pr.col8 = pl.f4
WHERE date(pc.fechaHora_Ar)>= current_date() - INTERVAL 1 DAY AND Time(pc.fechaHora_Ar) BETWEEN '20:00:00' AND '23:59:59' or
date(pc.fechaHora_Ar)>= current_date() AND Time(pc.fechaHora_Ar) BETWEEN "00:00:00" AND '14:00:00' and pl.detail != "0";
------ AFTER CHECKING IN EVERY TABLE WITH SHOW CREATE TABLE# --------
................................................................................................
.................................................................................................
.................................................................................................
..................................................................................................
And while looking deep, I can see that have same exact datatype as the column
I have this memory with SELECT @@innodb_buffer_pool_size; ->
8388608
That is the default 8 mb. Besides the indexes, you'll want to increase this value to some percentage of what is available.
1 Like
Is this memory applied in general ?
Ok great thank you. I notice neither of these columns are indexed or keys, I would therefore create an index on tablesubgeneral.f4 and tablepr.col8 and see how this improves performance, right now that join is probably doing full table scans though from your previous post it doesn't seem like these have too many rows, but still better to have it now then not if these tables are expected to grow.
The other thing I noticed that is a bit odd to me at least is you have tablesubgeneral have a foreign key of (d2, d3) to tablegeneral which is fine, it's not odd to have a multiple column foreign key, but what does stand out is then you only JOIN USING (d2) - I think that means the index from the foreign key can't be used or is of limited use. I would recommend you change this to USING (d2,d3) since that is what your foreign key definition says the FK relationship is, or if in this case it really should only be on column d2, then index tablesubgeneral.d2 and tablegeneral.d2.
It's worth noting that KEY both_indx (d2,d3) means this is ONE key consisting of two columns, it is not making two separate keys, one for each column.
3 Likes
Good morning here!
Thanks!
I added both indexes, but still slow, I tried the options that you gave me, so I will still check on that, so I the last thing is increase the buffer pool size, I wanted to tried first the other thing.
I will export the database and execute the same query from my computer, because I am using a server through a VPN and see the buffer memory that I have in my pc and then see if there is a difference.
Ok. You should try to check the execution plan again, and see if you can get the visual one from MySQL Workbench from Query->Explain Current statement, it's a bit easier to understand If there's still full table scans going on, fixing that first will probably yield the best improvements, and more hardware/configuration will only work for so long until the tables get large. Always try to make the query right first imo.
3 Likes
Thanks, I will try, but how do you know that is doing a complete scan of the tables?
You'd see something like this
1 Like
just getting that label as full table scan?
Yes, I think in the tabular view there's some output for it as well that says "full table" or something but that escapes me. The visual breakdown of the execution plan is easier to interpret if you can do that.
1 Like
Thanks, let me check and analyze
and thanks for your time
1 Like
I just let 2 indexes, in the tablesubgeneral and I got a faster answer:
d2 and f4
because I added before that two, I am not sure why had the delay even doind that and I tried and did not work, so then I added the index for d2 in tablesubgeneral and tableGeneral and still had a delay
so know I just remove the index d2 for tablegeneral.d2 and It worked, i just got one full table scan , before were two.
2 Likes
I will still check on that but for now is working, thank you so much everyone:
I would say that every comment was a solution because we found the answer after every comment. Thanks
So in conclusion I learned :
- Check the tools of mysql : show create table, explain (write before your query) and also see the excution plan when you excute a query, is the menu on your rigth , will tell you visually if you are scaning a full table and more information
- Optimization and structure of your querys
- If You are using filters, check with columns you are using and see if you have an index, because indexes help to filter deep
- You can separate your long query in two selects for example and use the UNION ALL to try
- Also after doing everything, you can look on your buffer pool size, could help :
SELECT @@innodb_buffer_pool_size;
1 Like
Glad it worked out. Cutting down from 2 full table scans to 1 definitely helps. So you removed the index from f4 and it ran faster, same with d2? that does confuse me a bit as that is used on a join, but perhaps I am misunderstanding.
Glad it worked out though, the Explain Current Statement should be the first thing you check out for slow queries,
1 Like
Sorry, maybe I don´t explain myself, I remove the d2 from tableGeneral and it worked, because from 16.### seconds, now is less than 1 second
Thanks for your time
1 Like
Do you think that a column as null with an index will affect if is null?
Not sure what you mean, a column that has a few rows with NULL? That shouldn't slow it down any more than having a value.
I had to optimize something today actually, so I can show you exactly what I mean. I had to change a query to do some calculations and it started taking over a minute to execute. I ran the explain and saw this -
Note the highlighted part - it tells you the cost of the operation (Red boxes are what you should try to fix first generally), and the attached conditions tells me exactly what part of my query is causing it (from a join clause).
I know view_listInvoices.idx is a primary key and already indexed but hd.invId is not indexed. Applying CREATE INDEX invIdInd on listhdthreads(invId); changed this from a full index scan to a non-unique key lookup -
Key lookups are much faster and this one index took the execution time from over 1 minute to under 1/5 of a second. Note the "row examined per scan" - for each record it had to look through the entire other table of hd. Now it is down to 1 row examined per scan.
This is how you can use the visual explain of MySQL to optimize your query.
2 Likes