June 18, 2013

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…multiple nodes as well. Note that in the demonstration each node will contain redundant, but non-accessed data for…query script will run one more more semicolon terminated SQL statements. The queries for the benchmark are in ~ec2-user/shard-query/queries…

Filed Under: MySQL

Post: Using UNION to implement loose index scan in MySQL

August 10, 2006 By 15 Comments

… have table people with KEY(age,zip) and you will run query something like SELECT name …in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple… put zip as first column in the index as it is more selective but it is good enough for …

Filed Under: Insight for Developers Tagged With: ,

Post: PHP Large result sets and summary tables.

July 6, 2007 By 8 Comments

…Tables can work for some cases in others script can be more efficient especially when multiple…SELECT to the temporary table with auto_increment column and fetch data from this table using auto increment column…

Filed Under: Insight for Developers Tagged With: ,

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

…in mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select…in set (0.00 sec) So, yes, you can concatenate MyISAM files, even when multiple keys are defined. Not for…five | | 6 | six | +—-+——-+ 6 rows in set (0.00 sec) So varchar columns are…

Filed Under: MySQL

Post: Multiple column index vs multiple indexes

August 22, 2008 By 13 Comments

… enough to fit in the systems memory. I’ve benchmarked simple queries using where clause which covers multiple columns: Q1 SELECT sum(length(… cardinality tables to show what difference large BETWEEN can make. Conclusion: For benchmarked queries we can see Multiple Column index beats Index Merge in …

Filed Under: Benchmarks, Insight for DBAs

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 you may put index… for order by. Sort by column in leading table if you have JOIN with ORDER BY … LIMIT you should try hard to have sorting column…

Filed Under: Insight for Developers Tagged With: ,

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

… could be reading the same page multiple times into the buffer pool. So…columns participating in the join condition and then a hash table lookup is performed, on the hash table…for highly selective queries, and obviously when the first operand used in the build step is such that the hash table fits in…

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

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

…multiple column indexes. This is in case you’re using OR between the columns. In…table to have i1 and i2 as independent columns in this case to look at more typical case: mysql [localhost] {msandbox} (test) > explain select…

Filed Under: Insight for DBAs, MySQL Tagged With:

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

March 4, 2013 By 7 Comments

…in the server’s error log: InnoDB: invalid column type for stopword table test/innodb_myisam_stopword. Its first column…for example, if we change the above query to be “+james +peterson arizona” then we might expect to get back multiple…

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

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…in, garbage-out and you risk dropping necessary indexes. To accomplish merging multiple…select * from all_indexes\G … *************************** 33. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: rental INDEX_NAME: rental_date NON_UNIQUE: 0 COLUMN_CNT: 3 COLUMN…

Filed Under: Insight for DBAs, MySQL, Percona Software
« Older Posts
Newer Posts »