I often see people confuse different ways MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes for query execution, which are not mutually exclusive, in fact some queries will use indexes for all 3 purposes listed here.
Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
In the recently released Sphinx version 0.9.9-rc2 there is a support for MySQL wire protocol and SphinxQL – SQL-like language to query Sphinx indexes. This support is currently in its early preview stage but it is still fun to play with. A thing to mention – unlike MySQL Storage Engines, some of which as InfoBright [...]
Converting Character Sets
March 17, 2009 By 21 Comments
The web is going the way of utf8. Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8. Googling for “mysql convert charset to utf8″ results in a plethora of sites, [...]
Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be faster but I also went ahead to do some benchmarks today.
Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
Today I was working with the client loading data to MyISAM tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which need to be inserted (in the table with few indexes). It was good not all records had to go to [...]
Missing Data – rows used to generate result set
July 20, 2008 By 15 Comments
As Baron writes it is not the number of rows returned by the query but number of rows accessed by the query will most likely be defining query performance. Of course not all row accessed are created equal (such as full table scan row accesses may be much faster than random index lookups row accesses [...]
Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
MySQL Server does not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what might be good practical reasons to specify the key name or omit ? Things what you should be looking at is how MySQL names indexes automatically as well as what maintaining [...]
Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
In the previous post I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from a bit more information. But lest first start with feature request for Innodb Team: All ways I mention here are hacks and they can’t be as efficient as native [...]
Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible. For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | CREATE TABLE `bbool` ( `b1` bit(1) NOT NULL, `b2` bit(1) NOT NULL, `b3` bit(1) NOT NULL, `b4` bit(1) NOT NULL, `b5` bit(1) NOT NULL, `b6` bit(1) NOT NULL, `b7` bit(1) NOT NULL, `b8` bit(1) NOT NULL, `b9` bit(1) NOT NULL, `b10` bit(1) NOT NULL ) ENGINE=MyISAM mysql> show table status like 'bbool' \G *************************** 1. row *************************** Name: bbool Engine: MyISAM Version: 10 Row_format: Fixed Rows: 10 Avg_row_length: 7 Data_length: 70 Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-24 00:41:01 Update_time: 2008-04-24 00:45:40 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) |
Multi-Column IN clause – Unexpected MySQL Issue
April 4, 2008 By 21 Comments
We have an application which stores massive amount of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows to find matching urls quickly. There is a bit of chance there would be some false positives but these are filtered out after reading the data so it works [...]
