June 20, 2013

Post: Implementing SchemaSpy in your MySQL environment

… will need a Java driver for your database — I’m using the MySQL Connector/J Install the graphiz package Creating a mysql.properties file…, and comments — a great way for a high level overview of the table sizes and an easy way to use the search feature of your browser to zero in on a particular table Constraints…

Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer

… use a caching layer to store expensive result sets or objects.  Some typical use cases include: Complicated query result set (search results… As a quick benchmark, I ran some batches of fetch and store against both a standard memcached instance and a minimally tuned MySQL 5… per/cmd 0.0951 ms range per/cmd InnoDB MySQL Select (same table): Fetching [1,000,000] items: 441573 ms total 0…

Comment: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer

…://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html there are some caching settings to play with: “Table cache… case, if memcached cannot find a key in memory, it searches for the value in an InnoDB table.” I’d like to see…

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

…: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_…a “+” in front of our search term, then that term *must* appear in the search results. But does it? With 5.5: mysql

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

… InnoDB full-text search in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically identical to MyISAM full-text search, in the sense that the SQL required to run a MATCH… the table does not contain a suitable column,” but there are only a handful of references to FTS_DOC_ID found when searching the MySQL

Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5

…even for read only workloads. Workloads which got a lot of attention such as Sysbench and … The query looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, …search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql

Post: MySQL Indexing Best Practices: Webinar Questions Followup

… server does everything itself. Especially when using a CMS where DB structure is prepdefined A: MySQL Server will not automatically define any indexes… table, not other index, also you only can have equality comparison as a search condition WHERE A=5 ORDER BY B will use index (Aa lot more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table

Post: How Percona does a MySQL Performance Audit

…. And besides, most applications have a lot of the same components. Tagging, friends, queues, click tracking, search, paginated displays — we’ve seen… itself OK, so here’s a very basic audit, whose scope I’ll keep within just a MySQL server. The first step is… this.) There are a bunch of other things I’ll look at, including checking for default users in the mysql.* tables, running mk…

Post: Sphinx search performance optimization: multi-threaded search

Queries in MySQL, Sphinx and many other database or search engines are typically single-threaded. That is when you issue a single query… src1 { type = mysql sql_query = SELECT id, text FROM table } source src1p0 : src1 { sql_query = SELECT id, text FROM table WHERE id % 3…, you may want these to be ranges by using a helper table in MySQL to define those ranges, mixing it with sql_query…

Post: Joining many tables in MySQL - optimizer_search_depth

… joins with few tables, – it is not clear how far from optimal plans do we get by using a greedy search. From the same discussion we can learn how automatic selection works – it picks value of min(number of tables… somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using 0 as…