#maria Log v0.1

logs Catalogue

This page loads from the db, refresh as and when. It displays 24 hrs
For older data http://marialog.archivist.info/previous.php, logs go back to 2009-01-18
Some user stats
Table logs
date_time
user
message
2016-08-23 15:32:35
Valerii
hi!
2016-08-23 17:10:35
montywi
dbart_: ping
2016-08-23 17:11:05
dbart_
montywi: yes?
2016-08-23 17:16:47
EzeQL
hi
2016-08-23 19:43:36
oddjob
hello, is there anything like postresql pararell query planned in the feature?
2016-08-23 19:47:45
tom[]
should i load timezone tables (from mysql_tzinfo_to_sql) once in a galera cluster? or on each node?
2016-08-23 20:27:27
noobish
So I have 2 identically defined tables in two different maria 10.1.16 instances. The same indexes defined for both. When a query is run which joins in this table to a view (via 3 constants and an equality reference on one of the view columns), they act differently. One is a ref type with 10-ish rows, and the other is an index type with almost all of the rows (20k). Yet if I load the same data in both tables, they execute the same. Am
2016-08-23 20:27:32
noobish
about indexes or is this normal?
2016-08-23 20:28:29
noobish
the index contains all the referenced columns from both the join and the select, in that order.
2016-08-23 21:59:52
nahtnam
Hey
2016-08-23 21:59:59
nahtnam
I have data that is similart to this: https://gist.github.com/nahtnam/098713e2f8b3b41afcf64e3b41d24131
2016-08-23 22:00:13
nahtnam
How can I construct an sql statement that will select all of the uuids that have both a start and stop row?
2016-08-23 22:09:01
nahtnam
I tried this: SELECT * FROM alerts WHERE status IN ("start", "stop");
2016-08-23 22:09:25
nahtnam
but think that is "start OR stop" instead of "start AND stop"
2016-08-23 22:11:26
MadMerlin|work
data for a single uuid spans multiple rows, so neither of those will do what you want
2016-08-23 22:11:37
MadMerlin|work
the easiest approach would be a self join
2016-08-23 22:11:40
noobish
without looking at your data, you could do a join on the same table for the stop condition, then put where primary.col = start and secondary_join.col = stop
2016-08-23 22:11:53
MadMerlin|work
^ basically that
2016-08-23 22:11:57
noobish
^ beat me
2016-08-23 22:12:50
MadMerlin|work
select * from t as t1 join t as t2 using(uuid) where t1.status = 'start' and t2.status = 'stop';
2016-08-23 22:16:17
nahtnam
Thanks all!
2016-08-23 22:27:15
noobish
can anyone help me with why EXPLAIN is choosing different 'type' values for 2 tables with the same structure and index, but different data (one is type ref, and the other index)?
2016-08-23 22:27:53
MadMerlin|work
the optimizer makes different choices based on data stored in the tables based on expected costs
2016-08-23 22:28:18
MadMerlin|work
optimize/analyze table may change the optimizer plan
2016-08-23 22:28:32
noobish
and digging into that difference would be extremely technical, I imagine
2016-08-23 22:28:34
MadMerlin|work
you can also add index hints if it still choses a poor index
2016-08-23 22:28:50
noobish
it's not considering my index, as far as I read the results
2016-08-23 22:29:05
noobish
or at least possible_keys is null
2016-08-23 22:30:44
MadMerlin|work
that's odd, you're sure the table schemas are identical?
2016-08-23 22:30:48
MadMerlin|work
with the same query?
2016-08-23 22:30:52
noobish
indeed
2016-08-23 22:31:05
noobish
I will copy pasta the two explain results
2016-08-23 22:31:10
noobish
just the one row
2016-08-23 22:31:27
MadMerlin|work
is it doing a table scan? i don't recall if that causes it to report no possible keys
2016-08-23 22:31:41
noobish
the type is index, as I understand that means it's doing a full scan
2016-08-23 22:36:28
GeNyaa
Is there a reason why this query takes super long but when I remove either subquery it goes faster? http://pastebin.com/mUFM1BBs
2016-08-23 22:37:05
GeNyaa
from 11 seconds to like few miliseconds
2016-08-23 22:37:23
noobish
stupid client won't let me copy the explain results... So forgive the formatting:
2016-08-23 22:37:38
noobish
type possible_keys key key_len ref rows extra
2016-08-23 22:37:39
noobish
index (null) idx_fal 138 (null) 20355 Using where; Using index; Using join buffer (flat, BNL join)
2016-08-23 22:38:22
noobish
now with the same data:
2016-08-23 22:38:25
MadMerlin|work
GeNyaa: is there a reason you're not using joins?
2016-08-23 22:38:56
GeNyaa
MadMerlin|work: I only need value data from survey table
2016-08-23 22:39:12
GeNyaa
I am not interested in other data around it
2016-08-23 22:39:24
MadMerlin|work
that's fine, but doesn't answer my question
2016-08-23 22:39:36
GeNyaa
I have never used join
2016-08-23 22:39:44
GeNyaa
Not sure how it works
2016-08-23 22:39:47
noobish
type possible_keys key key_len ref rows extra
2016-08-23 22:39:48
MadMerlin|work
now is the time to learn
2016-08-23 22:39:49
noobish
ref idx_fal ifx_fal 152 al.facility 12 Using where; Using index
2016-08-23 22:40:32
GeNyaa
MadMerlin|work: I am not sure how join works
2016-08-23 22:40:55
MadMerlin|work
GeNyaa: now is the time to learn
2016-08-23 22:41:03
noobish
surely you can google a few precious tutorials?
2016-08-23 22:41:18
GeNyaa
I'll take a look
2016-08-23 22:41:41
noobish
simple example to start: select a.*, b.* from a left join b on a.col = b.col
2016-08-23 22:43:49
MadMerlin|work
noobish: it's still using the same index, key is idx_fal and key_len is >0
2016-08-23 22:44:15
MadMerlin|work
the key lengths are different though, so it's not using the same number of columns
2016-08-23 22:44:16
noobish
but without possible_keys, that means the optimizer is not utilizing it?
2016-08-23 22:44:33
MadMerlin|work
no, it is
2016-08-23 22:44:34
noobish
I noticed the length difference, I cannot explain it
2016-08-23 22:44:49
MadMerlin|work
key_len tells you how much of the index is being used
2016-08-23 22:45:03
MadMerlin|work
index (int, int) with key_len 4 means only the first column is being used
2016-08-23 22:45:09
MadMerlin|work
8 means both are used
2016-08-23 22:45:10
noobish
mm
2016-08-23 22:47:04
noobish
collation is different between them...
2016-08-23 22:48:46
noobish
but that didn't affect it when I set the the same. I dunno, still trying to find a difference. Thanks for your insight on the key length
2016-08-23 23:09:14
noobish
MadMerlin|work: interesting, the extended explain shows the optimizer flipped the order of one of the conditions, a not null check on the primary table. It replaced it with a const check on one of the joined table fields. I bet this is the difference between them.
2016-08-23 23:10:07
MadMerlin|work
there you go, different data, different optimizer plan
2016-08-23 23:11:08
noobish
yeah. I need to dig into how the optimizer does it's business I guess, or force it my way with a different query.
2016-08-23 23:11:24
MadMerlin|work
index hints are also an option
2016-08-23 23:11:34
noobish
I'm not familiar?
2016-08-23 23:11:54
noobish
found the KB on mariadb
2016-08-23 23:11:57
noobish
thanks again, I'm out
2016-08-24 03:58:06
elenst
group_concat
2016-08-24 04:06:38
jak2000
hi all
2016-08-24 04:07:08
jak2000
how to disable the lowewrcase/upercase of the tablenames?
2016-08-24 07:59:55
Rico
hi !
2016-08-24 10:57:54
montywi
MadMerlin|work: just wanted to say that I am now looking at your issue