|Showing entries 1 to 27|
Traditionally, the MySQL storage engine interface has taken a
minimalistic approach to data definition language. The only natively
supported operations were
RENAME TABLE. Consider the following
CREATE TABLE t(a INT); INSERT INTO t VALUES (1),(2),(3); CREATE INDEX a ON t(a); DROP TABLE t;
CREATE INDEX statement would be executed roughly
CREATE TABLE temp(a INT, INDEX(a)); INSERT INTO[Read more...]
You often see databases with huge dynamic text fields, such as VARCHAR(255), TEXT, or as I recently was allowed to see the blanket use of LONGTEXT (max 4GiB) in order to be invulnerable from all contingencies. Things getting even worse when an index is used over such columns, because hey, there is an index. It makes things fast :-) Okay, jokes aside. Often you can save a lot of space and time, MySQL spends traversing the index when using a proper column type and index size.
Today letâ€™s talk about a resource very useful on MySQL, the FullText Index and Search
This resource is very powerful, today on versions 5.5 is just available to MyISAM engine, but, like we can see on MySQL FullText documentation, it will be available also to InnoDB on MySQL 5.6
Usually when we want to search for a word or expression, we use LIKE â€˜%word%â€™, in case we are looking for more than one word we use LIKE â€˜%word1%word2%â€™, what many people donâ€™t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with FullText Index
the syntax is easy, MATHC() â€¦ AGAINST (), where MATCH we specified the name(s) of column(s) which we are looking
451 Research has today published a report looking at the funding being invested in Apache Hadoop- and NoSQL database-related vendors. The full report is available to clients, but non-clients can find a snapshot of the report, along with a graphic representation of the recent up-tick in funding, over at our Too Much Information blog.
The title of the talk is “How better indexes save you money”. Saving money? Hey sure thing :) I’m in Ronald.
For those of you who do not know Ronald Bradford, he’s an Oracle Ace Director in the MySQL field, a long time community contributor and a MySQL expert.
I hope to see you at 902 Broadway New York, NY on Tuesday 22nd March 6pm.
MySQL keeps many different files, some contain real data, some contain meta data. Witch ones are important? Witch can your throw away?
This is my attempt to create a quick reference of all the files used by MySQL, whats in them, what can you do if they are missing, what can you do with them.
When I was working for Dell doing Linux support my first words to a customer where “DO YOU HAVE COMPLETE AND VERIFIED BACKUP?” Make one now before you think about doing anything I suggest here.
You should always try to manage your data through a MySQL client. If things have gone very bad this may not be possible. MySQL may not start. If your file system get corrupt you may have missing files. Sometimes people create other files in the MySQL directory (BAD). This should help you understand what is safe to remove.
Before you try to work with one of[Read more...]
In browsing the BZR tree for lp:mysql-server, I noticed some rather exciting code had been merged into the Innobase code.
You may be aware that InnoDB will do some index dives when opening a table to get some statistics about the indexes that can help the optimiser make good query plans.
The problem being that this is many disk seeks. It means that on server restart, you have to spend a whole bunch of time seeking around the disk reading index pages.
Not any more.
There is now code merged in to store the calculated statistics in a table inside InnoDB so that these index dives don’t have to happen on startup.
Originally, this looked like it was going to make it into InnoDB+. The[Read more...]
Loose index scan in MySQL can really help optimizing “group by” queries in some cases (for example, if you have only min() and/or max() as your aggregate functions). For example, if you have this query (to find maximum delay for all US flights with departure on Sundays in 2010):
select max(DepDelayMinutes), carrier, dayofweek from ontime_2010 where dayofweek = 7 group by Carrier, dayofweek
the usual case will be adding a covered index on (dayofweek, Carrier, DepDelayMinutes). And MySQL will use this index fine (using index mean it will use the covered index):
[Read more...]mysql> explain select max(DepDelayMinutes), Carrier, dayofweek from ontime_2010 where dayofweek =7 group by Carrier, dayofweek\G *************************** 1. row *************************** id: 1 select_type:
Warning, the following is quite ugly, but does the job :)
A while back I needed to create an archive slave database from a half a terabyte myisam master and had space restrictions. I could not dump the db, load it, then drop keys (archive doesn’t support keys apart from a primary key on one column as of 5.1), alter engine etc (would take even longer than it took either way). So an ugly single liner came to mind and worked nicely too.
mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 | sed 's/ENGINE=MyISAM/ENGINE=archive/g' | grep -v '^ UNIQUE KEY' | grep -v '^ KEY' | perl -p0777i -e 's/,\n^\)/\n\)/mg' | mysql -uuser -ppassword -h127.0.0.1 -P3307 dbname
So what is it doing?
mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname
Geo spatial indexes are what make this type of data valuable. With shape and point data you can find relationships between object in our physical world. How close is the lightning in the storm front? What homes where hailed on? (WDT) What schools are in my city? With a list of homes for sale, how fare are they from their nearest school? What picture where take in this area. (TwitPic)
Lets start with a simple grid of coordinates by creating a table for it call geom, adding our data points in and out of our grid and then searching with a small bounding box. The grid looks like this.0,0 0,1 0,2 1,0 1,1 1,2 2,0 2,1 2,2
CREATE TABLE geom[Read more...]
Tokutek’s Bradley did a session on their Fractal Tree Index technology at the MySQL Conference (and an OpenSQL Camp before that – but I wasn’t at that one), and my first thought was: great, now we get to see what and where the magic is. On second thought, I realised you may not want to know.
I know I’m going to be a party pooper here, but I do feel it’s important for people to be aware of the consequences of looking at this stuff (there’s slide PDFs online as well as video), and software patents in general. I reckon Tokutek has done some cool things, but the patents are a serious problem.
Tokutek’s technology has patents pending, and is thus patent encumbered. What does this mean for you? It means that if you look at their “how they did it” info and you happen to code something that later ends up in a related patent lawsuit,[Read more...]
In MySQL significant performance improvements can be achieved by the correct use of indexes. It is important to understand different MySQL index implementations and one key improvement on indexes defined on single columns is to use multiple column or more commonly known concatenated indexes.
However it’s also possible to define ineffective indexes. This example shows you how to identify a concatenated index that is ineffective.
CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, a INT UNSIGNED NOT NULL, b INT UNSIGNED NOT NULL, c INT UNSIGNED NOT NULL, d INT UNSIGNED NOT NULL, x VARCHAR(10), y VARCHAR(10), z VARCHAR(10), PRIMARY KEY (id), UNIQUE INDEX (a,b,c,d) ) ENGINE=InnoDB; INSERT INTO example(a,b,c,d)[Read more...]
Oracle’s plans for Sun’s OSS. The UK’s updated OSS strategy. And more.
Oracle’s plans for Sun’s OSS
# Oracle’s MySQL strategy slide.
# eWeek reported that database thought leaders are divided on Oracle MySQL.
# Zack Urlocker is leaving Oracle/Sun/MySQL.
# Red Hat’s Mark Little[Read more...]
I just discovered today in the MySQL 5.1 Reference Manual a handy set of additional indexes in the System Navigation section.
Perhaps they have been around for some time and I’ve not noticed, but there are much better then searching when you know the content type as per the index list on what you are searching for.
In the MySQL Community team, our charter is to serve the MySQL community — new and old MySQL users alike. One of the ways we do this is by facilitating information exchange between community members, where the new can learn from the old.
And there’s been lots of that information exchange going on, such as over mailing lists (in the early days the dominant vehicle), forums, and Planet MySQL.
One problem with this information exchange has been its ephemeral nature. The same questions pop up for many new users, and should they for some reason not be amongst the issues solved in the MySQL documentation, chances are you’ll have to[Read more...]
If you, like me, are building or thinking of implementing a MySQL-powered application that has any need for prioritizing selecting certain data over other data, this article is for you.
As a real world example, consider a queue-like video processing system. Your application receives new videos and processes them. The volume of incoming videos can at times be higher than the processing rate because the process is CPU bound, so occasionally a pretty long queue may form. You will try to process them as fast as you can but…
Note that I am using a queue here, so the the next item to be processed is a result of sorting by some sort of field in a ascending order, for example ORDER BY id or ORDER BY upload_date. I’ll pick the id sort here.
…suddenly, you[Read more...]
CREATE TABLE `items_text` ( `item_id` bigint(20) NOT NULL, `fts` varchar(4) NOT NULL default 'grzr', `author` varchar(80) NOT NULL default '', `title` varchar(255) NOT NULL default '', `content` text NOT NULL, PRIMARY KEY (`item_id`), FULLTEXT KEY `title` (`title`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `fts` (`fts`), FULLTEXT KEY `content` (`content`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
select count(*) from items_text where (MATCH (title, author, content) AGAINST ('+iron +man' IN BOOLEAN MODE))
Properties:Applicable To MySQL Server Server Startup Option
--log-queries-not-using-indexesScope Global Dynamic Yes Possible Values Boolean Default False Category Performance, Monitoring, Best Practices
If you have slow query logs enabled (with
--log-slow-queries), this variable will help interpret all those queries that are not using indexes as slow queries.
While designing a MySQL oriented application, I generally design my schema first without introducing any index (only exception being the PRIMARY ones). Get the application ready. Next enable[Read more...]
How often do you think about the reasons why your favorite RDBMS sucks? Last few months I was doing this quite often and yes, my favorite RDBMS is MySQL. The reason why I was thinking so because one of my recent tasks at Scribd was fixing scalability problems in documents browsing.
The problem with browsing was pretty simple to describe and as hard to fix - we have large data set which consists of a few tables with many fields with really bad selectivity (flag fields like is_deleted, is_private, etc; file_type, language_id , category_id and others). As the result of this situation it becomes really hard (if possible at all) to display documents lists like “[Read more...]
Properties:Engine(s) MyISAM Server Startup Option --key_buffer_size=<value> Scope Global Dynamic Yes Possible Values Integer
This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.
Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the[Read more...]
When using a uniqie index on a text field in mysql, the column collation setting is very important. The collation settings of a column does not only affect sorting and comparsion, but also unique indexes. So you can not insert "a" and "A" into a table that has a unique index on a column that has a case-insensitive collation. The mysql manual about collations: "A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set."
Here is an example:
The column text in table text1 has a case-sensitive collation (_cs suffix), the column in text2 has a case-insensitive collation (_ci suffix).
|Showing entries 1 to 27|