June 18, 2013

Post: Using any general purpose computer as a special purpose SIMD computer

May 16, 2011 By 5 Comments

… version as run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1… NULL ) The other important optimization combines results from multiple queries together. This query is single threaded, and thus this serves no purpose…

Filed Under: MySQL

Post: Recovering from a bad UPDATE statement

October 19, 2012 By 5 Comments

…16:10:42 server id 1 end_log_pos 532 Query thread_id=1 exec_time=0 error_code=0 …version of @2 to the new version of @2, etc. Reverse the entries row entries and create new UPDATE statements that…ROW — MIXED doesn’t help as in my testing as MySQL chooses to binlog UPDATEs in STATEMENT format when in …

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

… top 10 most expensive queries, in total execution time. By the way, the slow query logs in the stock MySQL server are extremely… in the slow query log output. The stock MySQL server’s limitation of one-second granularity makes it hide problem queries that are… the discussion to topics such as monitoring, alerting, backups, caching, reverse proxies, and high availability solutions, all of which we can…

Filed Under: Insight for Developers Tagged With:

Post: Handling big result sets

June 26, 2006 By 13 Comments

…query $query = ‘SELECT * FROM longf’; $result = mysql_query($query) or die(‘Query failed: ‘ . mysql_error()); // retrive result while ($line = mysql_fetch_array($result, MYSQL_NUM)) { } // Free resultset mysql…the string and client has to do reverse conversion. This takes time. Furthemore string data…

Filed Under: Insight for Developers Tagged With: ,

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

February 26, 2013 By 1 Comment

…, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0 rows affected…) mysql> insert into dir_test_innodb (full_name, details) SELECT reverse(full_name), details FROM dir_test_innodb WHERE id < 500000; Query OK… (0.00 sec) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql> optimize table dir…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: MySQL Error control changes

February 19, 2007 By 4 Comments

… one case however reverse change was done – in regards to storage engine initialization. Previously if you start MySQL and Innodb storage engine… but forgot to delete old ones) MySQL Server simply would not start. In recent MySQL 5.0 series however it will continue… often monitors MySQL is up and running and will not query all tables are accessible not catching such errors. If MySQL is started…

Filed Under: Insight for Developers Tagged With:

Post: Descending indexing and loose index scan

May 9, 2006 By 6 Comments

… in reverse order and it will well be. This is how MySQL will optimize indexed ORDER BY col DESC queries for example. Reverse scan… same query could retrive data in aready sorted order. This is however something you can workaround by having something like “reverse_date” column and using it for sort. With MySQL 5.0 you even can use…

Filed Under: Insight for Developers Tagged With: , ,

Comment: Full text search for all MySQL Storage Engines

June 28, 2006 By 37 Comments

… the mysql codes needed to store these bytes). If you have an average of 100 words per docid this means that reversed… linuxbox (the search query contained 10 most common words found in the database, linux 2.6 and mysql 4.1.18 was… in order to avoid hitting the query cache during tests): Reversed index without cheating: 24.0 seconds Reversed index with cheating (limit each…

Filed Under: Insight for DBAs Tagged With: ,

Post: To pack or not to pack - MyISAM Key compression

May 13, 2006 By 4 Comments

…to be treated differently. For uncompressed index blocks MySQL can do binary search inside the page – kind…some benchmarks. I’m testing 4 types of queries: select count(*) from t1, t1 t2 where …of uncompression than to traverse large memory areas. Reverse index scan performance got some 8 times faster…

Filed Under: Insight for DBAs Tagged With: , ,

Post: ORDER BY ... LIMIT Performance Optimization

September 1, 2006 By 100 Comments

… stick to simple MySQL using multiple indexes on most selective columns would be good idea for performance of such queries. For example…) but MySQL can’t do it at this point. Workaround which can be currently used is separate column which holds reverse values… end up in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select * from…

Filed Under: Insight for Developers Tagged With: ,
« Older Posts