… IMDB XML data sliced to sit within the VARCHAR(1000) fields per client requirements. First off the schema tables. CREATE TABLE…. mysql> select id from archive_table order by id desc limit 1; +———+ | id | +———+ | 1143883 | +———+ 1 row in set (2.04 sec) mysql> select id…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…’ application we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state”, “gender… in this case because there is really no join: mysql> EXPLAIN select SQL_NO_CACHE c.city from cities_join c JOIN… performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…, appr.key vals 306195, leaf pages 2189, size pages 2212 FIELDS: emp_no from_date [...] The second method has the same….01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select * from salaries where emp_no=10008; +——–+——–+————+————+ | emp_no | salary…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
… manual, here is how MySQL handles timezone locality with timestamp fields: Values for TIMESTAMP columns …| this_crc | master_crc | +———-+————+ | 763d97f1 | 763d97f1 | +———-+————+ Node2 > EDT > select this_crc, master_crc from percona.checksums; +———-+————+ |…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql… to the database at all (use mysql -A in this case, to prevent it from getting field list from all tables) Reads… values (“a”,1) | 0 | 0 | 1 | | 10304 | root | localhost | dumptest | Field List | 15 | Waiting for table flush | | 0 | 0 | 4 | | 10305…
Comment: How to check MySQL Config files
December 27, 2011 By 7 Comments
… = mysql_connect(“”.MYSQL_HOST.”", “”.MYSQL_USER.”", “”.MYSQL_PASS.”") or die(mysql_error()); mysql_select_db(“”.MYSQL_DB.”",$conn) or die(mysql_error()); $sql = “SELECT * FROM test”; $res = mysql_query($sql); while ($field = mysql_fetch_array($res)) { $id = $field['id']; $name = $field…
Comment: Feature Idea: Finding columns which query needs to access
June 7, 2009 By 16 Comments
…Just getting ugly now. Last try: $con = mysql_connect(‘ip’, ‘user’, ‘pass’); mysql_select_db(“db”, $con); // Fill in your …”; $result = mysql_query($sql); while($row = mysql_fetch_array($result)) { $field=$row['Field']; $sql2=”create index $field on test($field);”; $result2 = mysql_query($sql2); } …
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
… I’ve omitted everything by few rows. mysql> select * from node procedure analyse() \G *************************** 1. row *************************** Field_name: percona_drupal.node.nid Min… NULL Here is how this table was originally defined: mysql> desc node; +———-+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra | +———-+——————+——+—–+———+—————-+ | nid | int(10) unsigned…
Comment: Four ways to optimize paginated displays
September 24, 2008 By 27 Comments
… primary key value) 3. SELECT fields FROM table WHERE id IN (collectedIds) 4. re-arrange ids 5.SELECT FOUND_ROWS(); 1 usually requires… 3. This takes as little time as it could take; mySQL can fetch the record information for the rows required as…. SELECT fields FROM table WHERE id IN (collectedIds) 4. re-arrange ids 5. If we originally got size + 1 rows back from select…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’G *************************** 1…” field is selected in the list of columns to show (click show/hide to change the columns) That the “public dns” field is selected…
