March 2014 - discuss - lists.mariadb.org

Re: [Maria-discuss] Index bug in aria engine?
by pslawek83 07 Mar '14

07 Mar '14

Ok i wasn't able to reproduce the issue fully, now data is different ... but again, "skipped rows" number changes, and index sizes differ a lot: Uniq index: http://screencast.com/t/SrBS1L5xeB8 http://screencast.com/t/3dVMU7b69AH2 Normal index: http://screencast.com/t/Zl5Jm2OZ http://screencast.com/t/P3SA8U6duly But again, unique index is working much worse than not unique... while it's bigger http://screencast.com/t/rjexoWKwFF http://screencast.com/t/qYdsmRbRbmG7 I'll quickly explain how tables were created... i first took part of the data with unique index, and made smaller copy, thats table 1 Then i just copied the table changed the index to non-unique. After this i copied the tables once again to be sure files are not fragmented, etc. SHOW WARNINGS returns nothing in both cases. It seems that the additional knowledge (only single row with same data) should make the optimizer and query behave better but it's actually behaving much worse... -- Query for reference: EXPLAIN extended SELECT attrib_id, raw_stats_value_id, sum(impressions)FROM raw_stats_other_copyi1_copy INNER JOIN raw_stats_lookupON raw_stats_lookup_id = raw_stats_lookup.idWHERE domain = 'mydomain.com'AND date = '2014-02-10'GROUP BY attrib_id, raw_stats_value_id; -- Thanks, Slawomir. Dnia 6 marca 2014 17:46 Sergei Golubchik <serg(a)mariadb.org> napisał(a): Hi, pslawek83! On Feb 17, pslawek83 wrote: > Hi Guys, > any comments on this issue? It seems that partial unique indexes can't be used in joins. > https://mariadb.atlassian.net/browse/MDEV-5663 Please, show EXPLAIN EXTENDED (and SHOW WARNINGS after it) for this query of yours. > * Why the server is treating same indexes differently depends on if > they're UNIQUE or not It doesn't, there're almost no differences between UNIQUE and non-unique indexes from the optimizer point of view. Optimizer knows that for a unique index there can be at most one matching row (if all index parts are known and are NOT NULL), for non-unique indexes there can be more. That's basically the only difference. > * That's probably not optimizer issue, as we can't FORCE the index You cannot force the index if it is not applicable at all. > * What's internal difference between unique and non-unique index (eg. > memory / file representation / data structure) none. > * What each index type is suitable for, considering query optimization > (as there's no data i was able to find on topic) Aria only supports BTREE indexes anyway. Regards, Sergei

2 2

[Maria-discuss] Index bug in aria engine?
by pslawek83 06 Mar '14

06 Mar '14

Hi Guys, any comments on this issue? It seems that partial unique indexes can't be used in joins. https://mariadb.atlassian.net/browse/MDEV-5663 Regards,

4 5

[Maria-discuss] New Question: mariaDB for arm
by AskMonty KB 05 Mar '14

05 Mar '14

Hello, A new question has been asked in "MariaDB community" by mikestorcheous: -------------------------------- А не подскажите репозиторий для debian 7 armhf? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-for-arm/

1 0

[Maria-discuss] MariaDB & MySQL community event 3rd of April 2014 in Santa Clara
by Michael Widenius 05 Mar '14

05 Mar '14

Hi! I am happy to announce that the MariaDB Foundation is organising a MariaDB & MySQL community event in Santa Clara on Thursday the 3rd of April. The venue is the Hilton Santa Clara hotel, a short walk from the Percona Live 2014 event. You can find more information on my blog at: http://monty-says.blogspot.fi/2014/03/mariadb-mysql-community-event-2014-in… We already got some very interesting talks and speakers, but I would like to see even more talk proposals. It's ok to propose a talk that it's also hold at Percona Live. As this event is free, I expect a totally different group of attendees, so that should be fine! When we have got a nice list of proposed talks, we will post them here so that everyone has a chance of commenting of which talks should be accepted. (Sorry, but this time we don't have time to create a proper selection committee so we have to do this a bit rough) Personally I will talk about MariaDB 10.0, which should be declared stable by then. Hope to see many MariaDB developers and users in Santa Clara! You can sign up for the event and/or dinner at: http://www.eventbrite.com/e/mariadb-mysql-community-event-2014-tickets-1080… Regards, Monty PS: Feel free to discuss the event on this email list! We want to have as many as possible participating in making this the best possible MariaDB and MySQL event.

1 0