June 19, 2013

Post: Efficient Boolean value storage for Innodb Tables

April 23, 2008 By 6 Comments

…) column. So we can get 1 byte per column for boolean flag storage in Innodb (not accounting for standard row overhead…_length: 14680064 Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-24 01:14…_length: 14680064 Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-24 01:14…

Filed Under: Insight for Developers Tagged With:

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…create_eventblogpercona.commanual Com_create_functionblogpercona.commanual Com_create_indexblogpercona.commanual Com_create_procedureblogpercona.commanual Com_create_serverblogpercona.commanual Com_create_tableblogpercona.commanual Com_create….commanual ft_boolean_syntaxblogpercona.commanual …

Filed Under: Insight for DBAs, MySQL

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

… clustered index based on (in order) the primary key you specify (an auto_increment), a unique key, or one it creates for… factors that developers should consider when deciding when / how to create indexes. Foreign key references may add processing time to inserts, updates… other hand, an index that has very low cardinality (1/2 a million rows with an index on a Boolean column for example…

Filed Under: Insight for Developers Tagged With: , ,

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

March 4, 2013 By 7 Comments

…_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_myisam_stopword` ( `value` varchar(18) NOT …index, and tried again. mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST (‘”Thomas B Smith”‘ IN BOOLEAN…

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

Comment: How to find wrong indexing with glance view

August 23, 2008 By 27 Comments

…]. As all boolean operators are ‘AND’, mysql can use either indexes built on multiple columns or intersection of multiple accesses by indexes. When…, Gender, Age) is the most effective. Drop it :) and instead create (City, Gender, Age, LookingFor) and (City, Gender, Age, Available). So…

Filed Under: Insight for DBAs Tagged With:

Comment: Full text search for all MySQL Storage Engines

June 27, 2006 By 37 Comments

… has it limitations specially when used in boolean mode on large results (for example a boolean search that searches on common words… for mysql (I assume this means that the sphinx index can be created directly from within mysql just as with mysql own…/tbgsearch (unfortunately currently only available in swedish but I will create an english translation shortly (ehm, at least within months :P …

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL Server Variables - SQL layer or Storage Engine specific.

June 8, 2006 By 14 Comments

…can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance but… make sure data hits the disk ft_boolean_syntax, ft_max_word_len, ft_min_word… variable specifies maximum size implicit temporary table (created during query execution automatically) will grow as…

Filed Under: Insight for DBAs Tagged With: ,

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

… to process (it still searches the entire index though). Otherwise, takeaway is that this index has 10M records and it takes 700ms… records Let me illustrate that. I have created another full text indexed column for a skewed boolean attribute “ancient” which identifies books published…

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

Post: Sphinx: Going Beyond full text search

July 23, 2007 By 18 Comments

… pretty slow and we did not want to go into creating all this data as summary tables. Instead we decided to… positions in the index, while we do not need them in this case as we’re doing kind of “boolean full text search”. Plus we can make index built sorted by timestamp which would allow…

Filed Under: Insight for Developers Tagged With: ,

Comment: Full text search for all MySQL Storage Engines

February 12, 2010 By 37 Comments

… a sense, if it’s not critical to keep the index 100% up-to-date, it’s better to cron it…, and create a table or column that puts all the information together. Unfortunately, in MySQL, there are no expression-based indexes, but… helps someone. Also, reasonable results can be achieved by using BOOLEAN queries to restrict, and expressions to sort. AFAIK, natural FTS…

Filed Under: Insight for DBAs Tagged With: ,
« Older Posts