Loading Schema
I've been importing a database from a MSSQL server - not massive, 146 tables, about innodb_buffer_size 20GB total data and everything is fine with running queries - performance is good considering it's on a dev box at the moment.
Problem I have though is when you are using phpmyadmin or the mysql administrator it takes an age to load table data (in mysql admin it says 'Fetching schema table status').
These are innodb tables (I'm concerned myisam won't cope with us - lots of small updates and big slow queries) and if I convert the tables to myisam the problem goes away - loads fast as you like.
Is this a problem inherent to innodb or is there a configuration to get around it?
I'm running with default my.ini atm with the exception of innodb_buffer_pool_size=500M.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Schema Help
I'm building a database were I will have 1,000's of records information. This will include a unquie id for each row that contains name, street, city, phone (area code and 6 digits), URL, stock ticker if available, description, contact, date of contact, etc. (about 17 items in all) To make for faster look ups I intend to create a table where I can look for areacode and unique id. This way I can find all businesses in an area quickly. I'm using MySQL.
View Replies !
View Related
Db Schema
Have used Oracle and SQL Server for many years but have never worked with MySql yet. How can I get all the tables and their definitions? I'm assuming there are system tables that would store this information and that would be helpful. Is there a way, through a gui, to get this info? Is this gui, if exists, only for certain platforms? I need to start administering the schema and the gui option would be great for a start.
View Replies !
View Related
How To Log A Particular Schema
On the MySQL server which is hosting the schema that my application uses, there are other schemas too. As a result the MySQL log has information from the other schemas. Is there a way to filter out messages from my schema alone?
View Replies !
View Related
Data But No Schema
I have my data in a csv file which I will import into mysql. I need to develop the schema. I just want to use the column names in my csv file. Is there a tool that I could use which would recommend a schema? I have about 40 columns so to go and figure out each column would take alot of time.
View Replies !
View Related
Schema Vs Database
It seems that schema and database are interchangeable terms with the same meaning in MySQL, is that right? Is this how it is in other RDBMSs, or is MySQL different in this regard? Looking up briefly on other RDBMSs, it seems that they use database to refer to the database (good description, huh? ) and schema to refer to the "owner" of that database? I don't really know what the difference is, and how to define on versus the other. Can somebody explain the difference (better than my "a database is a database"), and why other RDBMSs see a need to do it this way (i.e. what's the benefit of implementing a whole other concept known as schema)? Does MySQL have plans to change things in the future on this issue? Or have I totally misunderstood the concepts of database vs schema, and how MySQL defines it?
View Replies !
View Related
Schema Evolution
I have just started using mySQL for some database needs and would like to learn how to modify a database to bring it in line with new schema definitions that I have. My changes are deletion of columns, marking of a column unique, merging of colums. There is also significant intercolumn relationship and I guess I would need to update that too.
View Replies !
View Related
Schema Suggestions
I've managed to create some fairly simple databases up to now, but need some suggestions on the best schema for this. I want to store some product details (inkjet cartridges) with the following data: Printer Manufacturer Printer Model Cartridge Ref No Cartridge Description Compatible Price OEM Price Reman Price I need some ideas for how best to go about doing this - what's confusing me is the how to cater for the fact that the Cartridge Ref No can apply to many Printer Models.
View Replies !
View Related
DB Schema Extraction
Is there a quick/easy way to extract the schema for an entire database so that I can quickly re-create the database on another server (without all the data)? I can use the GUI Query tool (or the Administrator -- not sure which), and select each table, one by one, and extract the SQL syntax to create that table, then append them all together in a single SQL file.. but that's tedious.
View Replies !
View Related
Export Schema Via SQL
I am needing to export the schema for a DB. Unfortunately I don't have command line access or access to a tool like phpmyadmin.. I only have access to it via PHP (Long story). Is it possible to export the schema for a DB via php? I've found the CREATE table LIKE table command.. But can that be used to create a table on server y based on a table on server x?
View Replies !
View Related
Relational Schema
How does one view the relational schema of an existing mysql database.... I also notice that there is a database called information_schema when I log-on to mysql comand line... using ubuntu....
View Replies !
View Related
Schema Support In My SQL
My second question is if MySQL supports schema why does my below code always returns me null instead of valid value. String url = "jdbc:mysql://localhost:3306/builder2"; Class.forName ("com.mysql.jdbc.Driver").newInstance(); Connection conn = DriverManager.getConnection (url, "root", "n0tall0wed"); metadata = conn.getMetaData(); String[] names = {"TABLE"}; ResultSet tables = metadata.getTables(null,"%", "%", names); while (tables.next()) { String tableSchema = tables.getString("TABLE_SCHEM"); System.out.println(tableSchema); } This code successfully works in other RDBMS databases.
View Replies !
View Related
Duplicate A Schema
In short: I want to make a carbon copy of a website that I want to use as the mysqllate for other websites. Server: winxp, mysql 5.0, php, apache, and Joomla content management system. All on localhost here. I can manage the apache and joomla copy, but want to know an easy way (in windows) to duplicate the schema in a similarly easy way. schema "site 1" I want to dupe to "mysqllate" then copy "mysqllate" to "site 2", "site 3", etc. In the same sense as a carpenter's jig.
View Replies !
View Related
Updating Db Schema
I was thinking something like mkdir dump mysqldump -p --tab=dump dbname mysql> drop database dbname; mysqladmin create dbname cat newschemafile.sql | mysql -p dbname mysqlimport dbname dump/*.txt But when I do it this way I seem to be left with the same schema I started out with.
View Replies !
View Related
Schema Does Not Exist? Yes, It Does!
I usually administer my database locally, but I've been experimenting with some .NET applications and decided I should learn to administer remotely so I'm trying to do so with the MySQL GUI tools... (Query Browser and Administrator) After some initial connection issues that turned out to be a firewall I successfully added a user and I'm atmysqlting to connect remotely now. When I enter my server's information (Host, port, user, password, and default schema) and click OK, I get a dialog box titled "Connection Dialog - Default Schema Does Not Exist" that reads "The schema 'opcdata' does not exist. Do you want to create the schema now?" (Yes, No) Clicking no takes me back to the connection dialog. Clicking yes results in a 1044 error.
View Replies !
View Related
Schema Change
I'm having a strange problem when trying to change the type of a field in one of my tables. I need to change the email column from varchar(40) to varchar(60). I do an ALTER TABLE tablename CHANGE email email VARCHAR(60); to make the change. The problem I'm having is that (this is a large table and I'd expect it to run for a few hours) the change runs for over a day and never completes and it doesn't crash the server but it kills network connections to the box. At least I think its a result of the jump - its the only thing of consequence running on the server.
View Replies !
View Related
Synchronizing Schema Changes
Is there a relatively easy way to synchronize changes to table structures without loosing data? What has happened is that my friend was desperate to use the project before I was even done determining what was needed. Even now he is requesting new features and fields but he has data he needs in his online version. What would the best method be to roll changes out from my development environment onto his live version without loosing data?
View Replies !
View Related
Mysql Schema
I was wondering if someone would be kinda enough to have a browse over my mysql database schema that I've drawn up. This is the 2nd one I've done up for this project ... a friend suggested some changes. So I've made them and this is what I've got so far. Code:
View Replies !
View Related
Database Schema Ideas
My company sells widgets. In our 'shopping cart' that we're building, we want to list out the price for each widget. The problem is that the price varies depends upon which widget package (out of 10) as user selects, as well as which website (out of 8) they visit. So, 80 different possible prices per widget (and there are lots of different kinds of widgets). Does anyone have any recommendations on how to build a database that will help prevent a separate record for each of the 80 different possible prices for each widget?
View Replies !
View Related
Copying A Table From One Schema To Another
Is it possible to copy a table from one schema to another schema by just copying the frm file to the directory for the new schema? What is the best way to create a new database with all the tables in it automatically? I was hoping to have the tables (the frm files) included in a subdirectory and when required, just create a new schema then copy all the frm files into it.
View Replies !
View Related
Schema And Catalog Under MySQL 3.23.55
I try to investigate some of the basics behind schemas and cataloges, Which is part of SQL2 Language this is the error message I get: C:mysqlin>mysql -u administrator -h localhost -p Enter password: ******* Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 246 to server version: 3.23.55-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql> use company; Database changed mysql> create schema company_00 authorization administrator; ERROR 1064: You have an error in your SQL syntax near 'schema company_00 author ization administrator' at line 1 mysql> create schema company_00 authorization administrator; === it seems to me that the sql statements schema and catalog is not working in my version of mysql. What can be done in order to remedy this ?
View Replies !
View Related
Database Schema Design
Each article/faq item can be associated with some topic (and both articles and faq items have nothing to do with each other) Right now i have 2 separate topics tables for articles and for faq items but these tables have exactly the same schema(same columns) The alternative is to use only one topics table and have additional column used to figure out if it is article's topic or other's.
View Replies !
View Related
Database Setup / Schema
I am new to the forums but have used the site for reference many times in the past. I am currently taking a directed project course at my university and my project is to develop a PHP/MySQL based statistical tracking program for a basketball team or teams. I have worked with both PHP and MySQL a little before and am hoping to become more proficient in both as I tackle this project. I'm trying to setup the database schema with DBDesigner. Here is a link to the BMP file and a DOC file with some needed fields and a sample txt file which the online app is supposed to handle: URL URL I have not yet setup the relations and need to make sure that my database is efficient and normalized. I also think that I may be missing a few needed tables and/or fields
View Replies !
View Related
Create Any Table With Name Schema
I created a database, then I try to create a table with name schema, but mysql don't allow me to do that, it says: "Database ERROR:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'schema ( vseq int(10) unsigned NOT NULL default '0', ' at line 1" why?if I change schema to schema2 or any other name the table will be created and no problem oocure.what is wrong in this command?
View Replies !
View Related
Copy MySQL Schema
i have a friend doing a project with me and we want to have same SQLschema name "projectez". He had created one i sent me the schema. i put it under the data folder but when opening the schema using a query browser it says that the table cannot fetch columns. why is this so? how to solve the problem?
View Replies !
View Related
Creating Schema DIFFs
I was wondering if anyone knows of a tool that can publish valid DIFFs for schema exports, we're working on rather large project and currently it takes a substantial amount of time to rebuild the whole db, yet the changes between each build are minimal when it comes to the db schema and it's data... I was hoping for something that will just isolate the difference and produce a valid script to bring up the schema to the last version.
View Replies !
View Related
How To Create Schema Dump
I need some help about how to dump only database schema, not the data. Our database already has data.But i want to create a database dump only for schema with out data. I think mysqldump dumps schema along with data. Are there any options i can specify which will let me to dump schema without data.
View Replies !
View Related
Change Schema Through Mysql...
I have a file .Sql and I want is to change the schema: -- -- Create Schema MySQL -- Create database IF NOT EXISTS mysql; USE MySQL; mysql through, it can be? i need this... -- -- Create Schema other_name -- Create database IF NOT EXISTS other_name; USE other_name;
View Replies !
View Related
Schema Privileges - LAMP
I have a Virtual Server from which I administer a few databases. I need to create complex queries including UNIONS. And to simplify my work, and to make objects available to my application I need the CREATE_VIEW privileges My "Root" tells me that MySQL 4.XX does not support the CREATE_VIEW privilege. My Global privileges do not have this value. How do I, or can I, add a CREATE_VIEW privilege to my list of available privileges?
View Replies !
View Related
Schema For Different Types Of Tables
Is there a website that offers schema for different types of tables. for example the code to create a table in MySQL 5 that you would use to keep data for registered users? heres one I made myself Create table users( login VARCHAR(40), NOT NULL password VARCHAR(40), NOT NULL first VARCHAR(40), NOT NULL last VARCHAR(40), NOT NULL address VARCHAR(40), NOT NULL zip VARCHAR(40), NOT NULL city VARCHAR(40), NOT NULL age SMALLINT, NOT NULL sex VARCHAR(10), NOT NULL phone INT, NOT NULL email VARCHAR(10), NOT NULL
View Replies !
View Related
Tagging Database Schema
I am making an application that will support tagging. Is there a preferred database structure to make this work? I would think that the best way is to make an n:m relationship between the table that is being tagged and a table of tags, with a minor table between them. Is this the best way or is there a better way? I am also looking for a way to tag multiple tables, (I have several things that I would like tagged: sites, pages, FAQs, messages, etc.), and I would like to somehow consolidate all of the tags into one table. Is this feasable or advisable?
View Replies !
View Related
Familly Tree Schema
~ to create my familly tree in a database, the basic schema I'm designing is something like this, can you please let me know if this is ok, or are there any other ways to make it better? table: persons (to hold person's personal info, profile..) - id - name - email - photo - etc. table: relatives - id (unique index) - person (id from table persons) - relative (id from table persons) - relationship (how does relative relate to person: father, mother, bro, sis, wife, son, daughter)
View Replies !
View Related
Advice On Hierarchical Schema
I'm trying to come up with a scema for a hierarchy of shopping categories. Each category can have many children categories and many parents. Ie. exist more than once, and at different levels of the hierarchy. There will be a limit of 5 to the depth of the tree, but some branches will terminate sooner. At the end of the branch will be a link to products within that category. I've read up on hierarchical structures, even read joe celko's book on the subject (I've just recovered from the resulting brain embolism ) but haven't really found solutions that seem to handle the many-many relationships going on, and in the case of the celko book there was a lot that mysql 4 can't do. I'm using version 4.1.20 (stuck on redhat and it doesn't support 5)
View Replies !
View Related
Mysql Dump Schema
I'm trying to get a hold of MySQL and am trying to dump the schema. This is the command I'm using: mysql> mysqldump -user root -password=rootpass flatland > flatland.dump; And this is the error I'm getting: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -user root -password=rootpass flatland > flatland.dump' at line 1 Any suggestions? I'm running the latest version of MySQL.
View Replies !
View Related
|
TRACKER
