June 20, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…: Doesn’t the primary key solution for random selection only work when the IDs for movies …from what you specify in the query? The tuple comparison you’re referring to is this example: WHERE… it would have to match the order of columns in the index.  Example: WHERE (last_name, first_name) = (‘Karwin…

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

Post: Gathering queries from a server with Maatkit and tcpdump

July 1, 2009 By 23 Comments

… 7517 _____ # This item is included in the report because it matches –limit. # pct total min max avg 95% stddev median # Count… ‘dual’\G # SHOW CREATE TABLE `dual`\G # EXPLAIN select 1 from ( select sleep(.1) from dual ) as x\G Indeed, it’s no surprise the… of a second to execute, and now you see where “SELECT dual” comes from. Notice that it is inspecting the protocol enough to…

Filed Under: Insight for DBAs Tagged With:

Comment: ORDER BY ... LIMIT Performance Optimization

October 23, 2006 By 100 Comments

… orders by a stactic field. I.e. SELECT * FROM WHERE MATCH() AGAINST(”) ORDER BY score DESC Where score is a precomputed score field. I have…

Filed Under: Insight for Developers Tagged With: ,

Comment: ORDER BY ... LIMIT Performance Optimization

October 23, 2006 By 100 Comments

Sorry, sql should read: SELECT * FROM WHERE MATCH(text_field) AGAINST(‘text’) ORDER BY score DESC

Filed Under: Insight for Developers Tagged With: ,

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

…> select * from catalog where match(‘ancientkey_1′); | total_found | 1499266 | | time | 0.227 | sphinx> select * from catalog where match(‘ancientkey_0′); | total_found | 8852086 | | time | 1.309 | sphinx> select * from catalog where match(‘solar’); | total_found | 2510 | | time | 0.001 | sphinx> select * from catalog where match…

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

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

March 4, 2013 By 7 Comments

… data set: mysql: SELECT id, title, MATCH(title, body) AGAINST (‘arizona business records’ IN NATURAL LANGUAGE MODE) AS score FROM seo_test_myisam… affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows affected (0.01… “no match.” This looks promising, so let’s explore further. Again, from the docs, if we run a boolean mode query where some…

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

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y” might use… taking longer – if we stopped after retrieving the 17k rows matching user_type=2, we’d save ourselves a lot of…: SELECT user_id FROM (SELECT user_id,parent_id,status FROM users WHERE user_type=2 AND user_id > 2938575 ORDER BY user_id) t WHERE…

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

Comment: ORDER BY ... LIMIT Performance Optimization

September 20, 2006 By 100 Comments

… do not understand. — SELECT * FROM sites WHERE MATCH(title, url, stripped) AGAINST(‘test’ IN BOOLEAN MODE) AND stat = ‘A’ ORDER BY match(title, url, stripped… SIMPLE sites fulltext stat,match_url_title_stripped match_url_title_stripped 0 1 Using where; Using filesort — Versus — SELECT * FROM sites WHERE MATCH(title, url, stripped…

Filed Under: Insight for Developers Tagged With: ,

Post: When the subselect runs faster

March 18, 2010 By 9 Comments

… in the cases when WHERE clause is not very selective. So I tried this: select * from table where id in (SELECT id FROM `table` WHERE (col1=’A'||col1… retrieve rows in sorted order and checking if it matches our original WHERE clause with subselects. It looks scary if we look… | index | NULL | PRIMARY | 4 | NULL | 549117 | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1=’B') order by id desc…

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

Post: Identifying the load with the help of pt-query-digest and Percona Server

December 29, 2011 By 3 Comments

… 14081299 # This item is included in the report because it matches –limit. # Scores: Apdex = 1.00 [1.0], V/M = 0…_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’\G This is the actual… could be to instead of selecting every column from all the tables involved in the query, probably selecting only the needed columns which…

Filed Under: Insight for DBAs, MySQL
« Older Posts