…6.10 sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0…. schema name information. Viewing SchemaSpy output A completed Relationships view of these three tables …/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and… queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be improved. But there’s a finite… in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… key constraint which implied an implicit secondary index with the same name. Fixed by detecting …(Alexey Kopytov). Removed trx_list scan in read_view_open_now() which is another problem originally … (Alexey Bychko). Percona Server for MySQL was missing help texts in the MySQL client because the help …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…empty transaction list when creating read views. However, the more general descriptors optimization…percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-…-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… key constraint which implied an implicit secondary index with the same name. Fixed by detecting …(Alexey Kopytov). Removed trx_list scan in read_view_open_now() which is another problem originally … (Alexey Bychko). Percona Server for MySQL was missing help texts in the MySQL client because the help …
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… solve the same problem having things more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from… very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from… be very careful implementing MySQL VIEWs in your application, especially ones which require temporary table execution method. VIEWs can be used with very…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… external sort. Q: How can we manage indexes on servers from DBA point of view ? Is there any management required or server… prepdefined A: MySQL Server will not automatically define any indexes for you. Hopefully your CMS already comes with reasonable set of indexes, if… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… space an index utilizes. Let’s go update our all_indexes view to include this information: mysql> drop view if exists all_indexes; mysql> create view all_indexes as… droppable_indexes view to use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But …sizeblogpercona.commanual tx_isolationblogpercona.commanual unique_checksblogpercona.commanual updatable_views_with_limitblogpercona.commanual Uptimeblogpercona.commanual Uptime_since_flush_…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
… very useful to view the result of a command spanning over many lines (for instance SHOW ENGINE INNODB STATUS): mysql> pager less PAGER set to ‘less’ mysql> show engine innodb status\G [...] Now you are… execution plans for a query by changing indexes. Discarding the result is possible with pager: mysql> pager cat > /dev/null PAGER set…
