Tag Info

Hot answers tagged

5

I'm afraid not. The ANSI SQL standard defines some data dictionary views in a schema ("INFORMATION_SCHEMA") that can be used to look for specific types of objects (tables, views, table columns), but it doesn't include anything regarding indexes. Most RDBMSes have their own internal data dictionary views that expose this information (sysindexes in SQL ...


3

When it comes to MySQL, you need to search the INFORMATION_SCHEMA database. The following tables will allow you to hunt down a column through all your indexes. INFORMATION_SCHEMA.KEY_COLUMN_USAGE INFORMATION_SCHEMA.STATISTICS TABLE DESCRIPTIONS INFORMATION_SCHEMA.KEY_COLUMN_USAGE mysql> show create table information_schema.key_column_usage\G ...


2

This is likely to be a bug in the version of MySQL 5.1 that you are running, causing the server to provide incorrect metadata to mysqldump when it uses DESCRIBE to learn about the composition of views. http://bugs.mysql.com/bug.php?id=65379 Background: any time you create a view that references a table, that table must already exist before you can create a ...


2

They are the same software. The -log suffix is simply appended to the version string at runtime. Here's an explanation (nearly from the last century), by long-time MySQL author Paul DuBois: I want to know what the -log means and how it got in there. -log is added if you start the server with logging turned on. -debug is added if you're ...


2

I see one of the comments in the question mentions caching indexes please note that only one index will be cached in the buffer pool (most probably PRIMARY, but check EXPLAIN to be sure). You may need secondary indexes in the cache as well, so you have to run additional SELECTs It just so happens I have post from Feb 04, 2012 that lets you make those ...


2

Please keep in mind OPTIMIZE TABLE is DDL not DML. In terms of mechanism, this is what OPTIMIZE TABLE mydb.mytable; does under the hood ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable; or CREATE TABLE mydb.mytable_new LIKE mydb.mytable; ALTER TABLE mydb.mytable_new DISABLE KEYS; INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable; ...


2

Does it increase memory ??? Absolutely !!! I wrote a post about INFORMATION_SCHEMA (Jun 15, 2011 : How is INFORMATION_SCHEMA implemented in MySQL?) In that post I explained how metadata for MySQL tables are stored in temporary tables using the MEMORY storage engine. For example, when you create a table mydb.mytable, look at what gets populated into these ...


2

3 seconds is kind of a long time for mysql. I bet you have a lot of full-table scans going on that are using up CPU. I've gotten good mileage in decreasing CPU with just a little indexing. This is not going to be a short answer, but try this: Enable the slow query log (set global slow_query_log=on and set global ...


2

What I would do is maintain a SQL script for the indexes that you've added to the "virgin" database. Store this script in a version control system, as you'll be adding indexes to fix performance problems. I would employ a naming strategy to make it easy to identify which indexes are "yours" and not part of the database as delivered. I'm not sure what ...


1

I don't know what you want in the explanation, but the last part of the query does a cross join (Cartesian join) to generate an ordered sequence of numbers from 0 to 11322 using the digits table named ints. The query then adds these number to date '2010-01-01' as day interval, thus generating '2010-01-01' '2010-01-02' '2010-01-03' ..... ..... '2040-12-31' ...


1

I will answer each the the three questions QUESTION #1 Since I'll probably move to mysql cluster, does it make sense to switch from InnoDB to NDB engine and use HASH indexes on "category" and "status" columns? ANSWER TO QUESTION #1 Hash indexes are for one-to-one lookups. Hash indexes are only available for the MEMORY Storage Engine (See my May 17, ...


1

Your Altered_Files design is absolutely fine. In fact I would award bonus points for separating the files per se from the hierarchy. @chillworld's suggestion is workable and very common but yours is better because of the NULL issues you mention. I can't think of any update which is better in one schema or the other. Change a single row - either the file ...


1

With large datasets, where data does not fit in RAM, you will warm your buffer pool -- and beyond. You will thrash the buffer pool. Your query does a full table scan. Assuming id is the PRIMARY KEY, you will be warming up the entire index tree (though not necessarily rows nor secondary indexes). But then you'll do the same for all your tables, and ...


1

You need to change java code to something like (for simplicity sake error handling and closing statements omitted) try { conn = ..... conn.setAutoCommit(false); Statement s1 = conn.createStatement(); s1.executeUpdate(/*first insert */); s1.executeUpdate(/*second insert */); // etc conn.commit(); } Side notes. 1) it's much ...


1

Perhaps this is better solution for you: SELECT COUNT(*) AS tables_found_count FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA` = 'database_name' AND `TABLE_NAME` IN ('table1', 'table2') It returns the count of found tables. Please list the tables in brackets and specify your database name instead of database_name.


1

Please try this query: select group_concat(foo_id separator ',') as ids from ( select case when locate('-', foo_ids) > 0 then substr(foo_ids, 1, locate('-', foo_ids) - 1) else foo_ids end as foo_id from foo_table union all select case when locate('-', foo_ids) > 0 then substr(foo_ids, (locate(foo_ids, '-') + 1), locate('-', foo_ids) ...


1

The tarballs are at the bottom of the list of downloads after you select "Linux - Generic" from the operating system drop-down... not under "Red Hat Enterprise Linux." Assumuming you are 64-bit, the one you're looking for is called "Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive."


1

The query select col1,col2 from mytable where col1 between a1 and b1 and col2 between a2 and b2 can be answered using index3 without accessing the table. If there is only index1 and index2 for example the following methods are possible: method I: 1) check for each row from mytable if col1 between a1 and b1 and col2 between a2 and b2 method II: ...


1

MyISAM didn't change between 5.5 and 5.6. It's not being developed, and it's virtually stagnant. Use FLUSH TABLES WITH READ LOCK and then it's safe to copy the MyISAM tables. This is mentioned in https://dev.mysql.com/doc/refman/5.6/en/backup-methods.html Other solutions: mysqldump --tab outputs tab-delimited data files instead of SQL, so it's much ...


1

LinkedIn has open-sourced their CDC tool Databus, which supports MySQL. http://engineering.linkedin.com/data-replication/open-sourcing-databus-linkedins-low-latency-change-data-capture-system Another open source CDC tool (implemented in PHP) was written as a part of the FlexViews project. http://swanhart.livejournal.com/132117.html


1

Analysis Since we are talking about InnoDB, let's zero in on the gen_clust_index. It's a special index that keeps the PRIMARY KEY and associated row data accessible from the same InnoDB pages. According to MySQL Documentation on the gen_clust_index If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. If you do not ...


1

As long as you don't have any additional data to store with the "rental" or "sell" property, you don't need a table for it. Just use a COLUMN manage ENUM('rent', 'sale'). Then you have to think about prices. The "BuyMe" price seems simple. Renting Models may have a real interesting live, I mean there are a lot of ideas which come in mind, and much more ideas ...


1

EDIT: Since the first try with a subselect lead into doom, it might be necessary to split the table into a hot range and a "cold" range. That solution only could help if you have some criteria for id_c and id_b, which shows if this id_c will be read often or rarely, for example the age of this. But even deleting from such a big table is not so easy, it would ...


1

The ALTER TABLE ... DEFAULT CHARACTER SET ... command sets the default character set and collation for the table. It does not affect the existing VARCHAR/TEXT columns and their data. Only newly created fields will use the specified charset and collation. The ALTER TABLE ... CONVERT TO CHARACTER SET ... command changes the character sets and collations of ...


1

You certainly have a problem but that does not look related to enabling general logging. Are you sure it's not that those errors have been filling your error log previously and you only just noticed them after an investigation stemming from enabling general_logging? Enabling general_logging can and cause your server seemingly hang. General logging will ...


1

Some best practices: Create a DBA user for yourself. This should be the only user with "WITH GRANT OPTION" in their permissions. This should also be the only user with select privileges on *.* because that includes mysql.user. Every user should have their own username and password, no shared accounts. They should each have permissions to specific ...


1

You definitely should disable old_passowrd. You'd need to regenerate them all to start using the new hash. (You probably could setup a script to brute force crack them all for you ;) Is you team the DBA team or developers. If DBA then yeah you are the guys that should have root access and any root password changes should be coordinated by the team and ...


1

What you seek is a table like the one below, from which you could query: SELECT * FROM departures WHERE route_id = 'SOME_ROUTE' -- This is the code to a route, encompassing from/where AND departure_date = @some_day AND departure_time_min <= @target_time AND departure_time_max >= @target_time I present a solution where such table is ...


1

Since you control my.cnf on EC2, you have two options, neither of which require restarting mysql OPTION #1 : Change old_password Protocol for EC2 Stop replication to the RDS Slave As root@localhost run this on EC2 : SET GLOBAL old_passwords = 0; Start replication to the RDS Slave OPTION #2 : Lower the Security for EC2 Stop replication to the RDS Slave ...


1

First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3). This column shall be kept up-to-date either by your application code or by triggers, such as: CREATE TRIGGER ... BEFORE UPDATE ... ... SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3) ... CREATE TRIGGER ... BEFORE INSERT ... ... SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3) ... ...



Only top voted, non community-wiki answers of a minimum length are eligible