… and performance? A: Recent MySQL versions are smart enough to convert id IN (5) to ID=5 (for single item…competition_entry user_id (INT), competition_id(INT); The table is only used to record a user_id and… VARCHAR(36) instead of auto-increment A: If you’re using UUID it is at least good to convert…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… NULL, `j` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1… it has to read row data from OS Cache. So what if we convert i and j columns to varchar while sticking to utf8 character… I decided to do is to convert Innodb table to latin1 character set. I was expected this to shorten some internal buffers MySQL has to allocate for…
Post: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
… better to define your columns as INT for many reasons, but if you decided to go with VARCHAR (ie you need leading zeroes to be preserved) you should refer to… using “”. You may ask why MySQL can’t use index in this case, simply by converting number to the string and performing index…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
…mysql> show create table test.tablesG *************************** 1. row *************************** Table: tables Create Table: CREATE TABLE `tables` ( `name` varchar(100) DEFAULT NULL, `spaceid` int… The customer was able to download the tables in MyISAM format and then he just convert them back to…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
…` int(11) unsigned NOT NULL auto_increment, `val` int(11) unsigned NOT NULL, `name` varchar(100) NOT NULL, `t1_id` int…The solution we proposed in this case was converting table t1 into InnoDB, and results: Threads … work to do in addition to fixes done in later MySQL 5.0 versions. We also decided to …
Post: Stored Function to generate Sequences
April 2, 2008 By 17 Comments
… me to help them to convert their sequence generation process to the stored procedure and even … // delimiter ; CREATE TABLE `seq` ( `name` varchar(20) NOT NULL, `val` int(10) unsigned NOT NULL, PRIMARY KEY … wonder why you would like to use sequences instead of MySQL auto_increment columns ? Leaving …
Post: Handling big result sets
June 26, 2006 By 13 Comments
…varchar(13) default NULL, `f27` varchar(39) default NULL, `f28` int(11) default NULL, `f29` float default NULL, `f30` int…to mysql_unbuffered_query and this link says: mysql_unbuffered_query() sends a SQL query query to MySQL…needs to convert millions of values from int to the string and client has to…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
… TABLE `t2` ( `id` int unsigned NOT NULL auto_increment, `char_id` char(6) NOT NULL, `v` varchar(128) NOT NULL…of 4.33 seconds to execute with t1 as utf8 and t2 as latin1. Converting both tables to utf8 resulted … time, from 4.33 to 3.12 seconds. This test was performed with MySQL 5.0.67, FreeBSD 7…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… varchar(100) and tb2.id – int(11) column. No matter what I did – forced it to use…number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE …numeric argument is converted to its equivalent binary string form; if you want to avoid that, …
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
… structure: CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, … you insert some test data into it: mysql> INSERT INTO example -> VALUES ( ->Â Â NULL… multi-byte characters into a VARCHAR(500) column, that value will… error after converting from a native language character set to utf8, as…
