June 19, 2013

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

… are sort of like unindexed MySQL columns. They are the extra details you may want to filter by, which is usually things… in a full text index that I can use for filtering: sphinx> select * from catalog where match(‘@userkey userkey_50′); … 20 rows… selective search and then filtering, using attribute is orders of magnitude better than using a fulltext key. That being said, for highly selective…

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

Post: How fast can MySQL Process Data

April 9, 2008 By 3 Comments

…=MEMORY DEFAULT CHARSET=latin1 mysql> select count(*) from m; +———-+ | count(*) | +———-+ | 1047684 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from m where…) the filtering speed stays about the same: mysql> select count(*) from m3; +———-+ | count(*) | +———-+ | 10476840 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from…

Filed Under: Benchmarks Tagged With:

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… on which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as…

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

Post: Filtered MySQL Replication

November 7, 2007 By 8 Comments

…filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough gotchas with replication itself and getting things right with filtering…ve seen such filtering being broken by developer forgetting this restriction and running one time update query without selecting proper default …

Filed Under: Insight for DBAs Tagged With:

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

…so there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row ***************************…, Shard-Query performance continues to improve when grouping and filtering is used. Again, notice Q9. It doesn’t use…

Filed Under: MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation ALL… where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders range…

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

Comment: MySQL VIEW as performance troublemaker

December 21, 2009 By 57 Comments

…, first select (view) is SLOWLY, then simple select (it’s obvious, becouse “group by” with index filtering is slowly, that simple index filtering). Problem not in VIEW MECHANISM in mySQL…

Filed Under: Insight for DBAs Tagged With: , ,

Post: How (not) to find unused indexes

October 16, 2009 By 2 Comments

…=latin1; mysql> SELECT count(*) FROM Country; +———-+ | count(*) | +———-+ |      239 | +———-+ 1 row in set (0.00 sec) mysql> SELECT count(distinct… row in set (0.04 sec) mysql> EXPLAIN SELECT * FROM country WHERE population > 100000000; # query 4 +—-+————-+———+——-+—————+————+———+——+——+————-+ | id | select_type | table   | type  | possible… lead yourself into a trap where your index can not filter out enough rows to be effective.  I see this…

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

Post: Using index for ORDER BY vs restricting number of rows.

February 16, 2007 By 8 Comments

… from different categories, different sellers different locations which can be filtered on, and there are also bunch of fields which sorting…`,`price`), KEY `cat_id_2` (`cat_id`,`seller_id` ) mysql> explain select * from goods where cat_id=5 and seller_id=1… where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where cat_id…

Filed Under: Insight for Developers Tagged With:

Post: MySQL Slow query log in the table

May 31, 2007 By 1 Comment

…of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you… interval (ie after I’ve done some changes) or filter out queries which you already looked at using LIKE… get new queries in the end you can do SELECT * FROM (SELECT * FROM slow_log ORDER BY start_time DESC…

Filed Under: Insight for DBAs Tagged With:
« Older Posts
Newer Posts »