If I Create An Index With Multiple Fields Do I Still Need To Create Individual Ones?
I have a table where the name field can only contain duplicates when the user_id field is different, so I create an index with user_id,name. Now I was wondering if I want to search just for the user_id or the name field individually does this dual field index count, or should I create another index for the user_id and the name?
View Complete Forum Thread with Replies
Related Forum Messages:
UDF In Create Index
Is it possible to use function UDF in CREATE INDEX declaration ? For example: CREATE INDEX innertext ON webpages (MY_UDF(html)). It's avoid the creation of an intermediate column with the data transformed.
View Replies !
Create Index
On an Oracle database it is possible to create an index for a table on a different tablespace. (create index userindex on user (userid) tablespace indices;) I did not found a similar statement in the MySQL Reference. Is there another way to do that?
View Replies !
Create The Effective Index
I want to create the best index for that query: "select * from talkback where status=0 order by bject_type,vertical_id,event_id" Which index i need to create for best performance.
View Replies !
Can't Create Index ... 1044
My user wood@localhost can't create an index on the databse woodwork... I get the error ERROR 1044: Access denied for user: 'wood@localhost' to database 'woodwork' mysql.user reads... +----------------+------------+ | user | index_priv | +----------------+------------+ | root | Y | | root | Y | | | N | | | N | | wood@localhost | Y | +----------------+------------+ anyone know why?
View Replies !
Create An Index Without Using All Available Connections
I've got a very large table (149 fields -- I know, I didn't create it, and I'm working on changing it...) and I need to add an index to speed up a join query. However, when I run the "create index `index_name` on table(`column`)", MySQL uses every available connection to attempt the creation of the index, and does not allow the web site to access the database. If this was a short period of time (a couple of minutes), I could stand it. unfortunately, this is a live production server, and I'm guessing it will take an hour or two to create the index. Does anyone know how I can run the "create index" query while limiting the number of connections the query uses? The version is: mysql Ver 12.22 Distrib 4.0.16, and "max_connections" is currently set to 100.
View Replies !
Killing An Index Create
I am running a create index on a large table. The job is taking an extremely long time. If I kill the create index process what are the ramifications in MySQL? Will it simply roll back the index? And is there any way to see a percentage remaining on the job?
View Replies !
Create Index On Date Field
I have a table: +-------------+--------------------------+------+-----+---------+------- ---------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------------------+------+-----+---------+------- ---------+ | id | int(11) | | PRI | NULL | auto_increment | | NUMFATT | int(5) unsigned zerofill | | | 00000 | | | dataemiss | date | YES | | NULL | | +-------------+--------------------------+------+-----+---------+------- ---------+ (the table has more fields but I removed them to semplify the view of it). Where I want to create a UNIQUE index with two tables 'dataemiss' and 'NUMFATT' 'dataemiss' is a date format and in the index I want to consider just the year part of the date. I cannot change the field type setting it as a string (ex. char, var, etc.). any hint on how to do it? The goal of this is index is to avoid to insert the same 'NUMFATT' twice or more in the same year. I'm using MySQL 4.0.13 on Win 2K server.
View Replies !
Create Index For A Mysqlorary Table
I have to do SQL query over two mysqlorary tables, I want to create indexes on fields of the mysqlorary tables. Is it possible to create index on a mysqlorary table. I am using MySQl 5.0.26 Furthermore, Is it possible to merge the statements for creating mysqlorary table and the statement for creating index on the mysqlorary table into one singel statement....
View Replies !
Create Index And Bookmark Script
I am using a bookmark script and have created a few sites with it.Sites run great when DB is small but the large site (190 MB) DB is hogging CPU I have been kicked out of two host for this last host suggested I create index on DB this is what the tables look like bookmarks table 189,134 rows bId uId bIp bStatus bDatetime bModified bTitle bAddress bDescription bHash Bid Bookmark Id is PRIMARY ties to tags table Tags this is probably where indexing would help the most as the google bots try and index the whole site all at once and kill the host Tags table 1,451,202 rows id bId tag id Primary key bId bookmark Id
View Replies !
Create Index Takes More Than 5 Hours
I'm running MYSQL on a windows xp laptop with an intel centrino 1.60 GHz Processor and 512MB RAM. I've created a table and imported 27 million rows of data. I wanted to create an index on one char(55) field, as this field is a key against which many selects will be made. The information in the field is not conducive to having an integer key as any queries would require an extra join to the look-up (reference) table. Creating the index ran for more than 5 hours and finally gave up when there was no more space on the drive (creating the index apparently consumed more space (11GB) than the actual table (8GB). I'm wondering if this is normal time and space consumption for 27 million rows. Finally, doing an un-indexed query on this table : SELECT * from historical where reference = 'abcd'; Takes more than 1/2 hour. Is this also a normal amount of time? I understand that people are using MYSQL for data warehousing and major transactional applications.
View Replies !
How To Create/choose A Suitable Index
I am having trouble figuring out a suitable index(es) in the following case: select * from circuit where (col1a=val1 and col2a=val2) or (col1b=val1 and col2b=val2); (it happens that I use val1 and val2 as above). Every choice I make for index doesn't, according to EXPLAIN, get used by the SELECT. The table has a few thousand entries and I expect the SELECT to return one or two at most. Well, it does, but quite slowly (in fact the SELECT is a bit more complex) and I would really like to improve this particular combination as its at the inner-most loop of my PHP code :-) INDEXes seem to work for ANDs but not when there's an OR in the month (sorry). So an index of columns A,B gets used if you have A AND B in the WHERE but not A OR B.
View Replies !
Create Key Consisting Of Two Fields
I am trying to create a table with a key consisting of two fields e.g. field one (page #), field two (section #). So that in the table I would have these unique combinations: page1, section1; page1, section2; page2, section1; page2, seciton2, etc.
View Replies !
How Do You Create Boolean Fields?
I have a database in which I want the ability to select certain records to display or not to display when it gets queried. I thought that just a simple checkbox on the data entry form would be nice and easy for designating this. And I assumed that this would be a boolean field since it's either checked or not.
View Replies !
CREATE INDEX Crashes Server (Lost Connection...)
I'm having trouble with a table I created quite a while ago and now want to add an index to. Here's what I get if I try to add an index on the host field of my table: mysql> create index Index_host on uselog(host); ERROR 2013 (HY000): Lost connection to MySQL server during query Connection was lost because the server crashed. The Windows event log has an event that says "Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x0017dc06." I've also tried the allegedly identical "ALTER TABLE uselog ADD INDEX Index_host(host)", as well as trying to index a different column, all with the same outcome (server crash). Thing is, I had no trouble adding an index to the same table a week or so ago. The table was smaller then (and had only one index), but even now, it's not very big by DB standards, so I can't imagine that's the problem. (My table had ~900K rows earlier today, which I pruned down to 140K rows after my first failed attempt to add the index. Puny! Only 55MB data length, according to MySQL administrator.)
View Replies !
Create URL From To Database Fields In Query Results
I have a MySQL database with table that works as document library. Table keeps details about docs on our server. There are bunch of fields but the key ones here are FileName, URL and location. I'm using PHP to work with the MySQL Database hosted on a Mac OS X Server. Basically, I have a query set up to pull certain records based on a field called Keywords. On the result page I want a field (or item) that combines the URL field and Filename field that ultimately points to the phycial location of the file. For every record thr url field contents is the same since the physical location of the files are in the same directory on the server. Right now the URL field has http://www.mysite.com/docs/. I'd like the result page to have the filename shown, but that filename would be a link to URL+filename. Is there a way to do this? .....
View Replies !
Unable To Create Full Text Search Index On A Column
I have installed MySQL 5.0 on my WinXP machine. I have created a test schema in which I have created a test table as follows: Test_Table { Id => Integer and Primary Key Name => Char(255) Address => Char(255) Tags => TEXT } Now I want to create a full text seach index on column Tags, but when ever I try to execute following SQL statement: ALTER TABLE `TEST_TABLE` ADD FULLTEXT `Index_FTS`(`Tags`); I get following error: Error while executing query: ALTER TABLE `TEST_TABLE` ADD FULLTEXT `Index_FTS`(`Tags`); MySQL Error Number 1283 Column 'Tags' cannot be part of FULLTEXT index. My table is MyISAM based table.
View Replies !
Allowing Users To Edit Or Create Fields In The Database
Here is what we want to be able to do: Users upload a flat-file containing their data. They will have custom column names. Is there any way the user can interact with the database to create or edit the field names? Or is it a matter of setting up a predefined number of fields the user will import data to, and then define a label to the column? Example: The database has a field called User1. The user is then able to name it for their own customization.
View Replies !
Can I Create Multiple Names For The Same Table?
Is it possible to create aliases for the same table? multiple names that point to the same table? I have a login table called d1_login, now I'm creating a new domain where all tables start with d2_ but I wanna use the d1_login table on the new domain. I can't hardcode the prefix and since all tables on the new domain starts with d2_, it refers to d2_login which does not exist so all queries fail.
View Replies !
Create Multiple Table Scripts
This requires the creation of a number of tables. I am sure there is a way to read a text file that contains the sql statements to create a number of tables but I cannot find the correct syntax or the correct command to use from the mysql prompt. I have the text files created for the table definition and I have the data in text files read for the "load data" command.
View Replies !
Create Multiple Tables From Table
I have a table called 'product' in a sql database called 'product_comparison'. There are currently 3 fields in this table. ProductNumber (which is charvar - 255), Description (which is charvar - 255), and Baillio's (which is decimal 20,2 **it's a dollar figure**). I have just under 10,000 lines (different products) and I'm wanting to create a table for each of the products. The tables should be named whatever the 'Product Number' is. Which is usually a combination of letters and numbers (thus the charvar). I also want to take the other 2 values and create 2 fields in their respective product tables. I'm thinking this should be an easy statement for people as smart as all of you. But I'm having trouble. So far, (with some help) I've come up with a statement something like this: CREATE TABLE PAGEONE SELECT * FROM product WHERE `ProductNumber` = 2; This doesn't work becuase 'ProductNumber' isn't an integer first off, and I'm thinking even if it did work, it would still just create one table called PAGEONE and not a table for each product like I want. Is that right?
View Replies !
How To Create Different Namespaces When Using CREATE VIEW?
I'm doing a triple join between tables which have similarly named fields...I was hoping that when using CREATE VIEW, the result would be a table with fields such as a.field, b.field, c.field...but I get an error "Duplicate column name 'field'" ...How can I get around this besides changing the name of the duplicate field in each table?
View Replies !
Using "mysqldump" To Export Individual Fields...
I've spent a lot of time searching around for an answer here, but have been unable to find one. What I'm looking to do is to use "mysqldump" to export two specific fields form a certain table, rather than exporting the entire table. This table contains near 20 fields, but I just need two. The command I'm using so far is: mysqldump -u USERNAME -p DATABASE --tables TABLE --xml > fields.xml I just need to know now how to fine-tune it so I can specifiy the two tables I need. I plan to use this command to regularly backup this particular part of the database, and I'm unsure if that can be done via another method or not.
View Replies !
INDEX'd Fields
I have a database, about 12 fields per record and roughly 100,000 records per month. I would do the following query: select count(*), type from TABLENAME where field0='23-23-23' group by type; the query would run an average of about 2secs. Not bad until I am running the same query about 1400 times all with slightly different field0 values. I added an index for field0...with: alter table TABLENAME add index (field0); Now those same queries run about an average of 1/10th of a sec!! My question is, is there a negative effect by indexing this field? Obviously you woudn't index all fields...but could you?
View Replies !
Adding Index Fields
in this situation: create table xyz as select a,b,c,d, from mytable group by a,b,c,d i wish to add an index to the table xyz on fields a and b how to do? (mysql 5.0)
View Replies !
Finding Constituent Fields In An Index
I don't know how to view all the columns of each unique index in a table. The describe command apparently indicates/marks only the 'first column of a unique-valued index'. What about the rest of the fields in the index?
View Replies !
Unique Index & Null Fields
I have a table with a particular field that needs to be unique and allow null values. According to the documentation: "A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL." ... which is exactly what I want. Except that's not what I'm seeing in my database. When I try to insert more than one null value into this column, the database throws an error: ....
View Replies !
Create New Db
is it possible(and how?) to make a new database in a specified folder - i.e. /home/me/mysqldb? or how may i install mysql on debian to use this folder for data files?
View Replies !
CREATE
I am going to be creating accounts for my database, and want some advice on how to set up my table. so my SQL will be something like: CREATE TABLE account ( account_id int unsigned not null auto_increment primary key, first_name varchar(20), last_name varchar(20), username varchar(20), password varchar(10), role_id int, creation_date TIMESTAMP); Here are my questions: 1) How can I get the creation_date to be automatically stuck in the table during the insert, rather than explicitly setting it? Is there a way to do this in MySQL? 2) role_id is the primary key of another table. Is there any way to specially indicate this? should it be an unsigned int? 3) username should be unique and not be allowed to be duplicated. Is there any way to enforce this criteria on inserts or updates?
View Replies !
Can't Create
While working with a particular Db instance on V. 5.0.20a-NT, I'm recieving the error: Can't create/write to file 'C:Program FilesMySQLMySQL Server 5.0Temp#sql_704_0.MYD' (Errcode: 17)(1) If I stop the instance and delete the temp file and then start the instance. This works for a bit but the error ultimately returns.Does anyone have any ideas as to what I could check or do?
View Replies !
Create DB With Ant
export-schema: BUILD FAILED com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: I'm working on Mac OS X. In Windows I have no problems doing this. All I do in XP is open MySQL Administrator, create the schema, create the user and grant all privileges, then from the command prompt run... ant clean deploy undeploy and then... ant export-schema
View Replies !
Create Db
I got a an sql file to restore a database from a phpmyadmin.i wanna use it for local, i use mysql > fichero.sql and dont let me restore connot connect to a database that its supposed to be created with that file,how do i created a db or to use that file in mysql.
View Replies !
Can't Create New Row!
i don't know what the problem here is! i set up a database on a server and made a form that you fill out and then php is used to make a new row with the info you fill out. so i export the database, move it to a new server and now the form doesn't work. i don't get any error, but no new data is added to the table. anyone have any ideas what could be going wrong?
View Replies !
Create A Foreign Key
why I should explicitly create a foreign key, especially if i'm not concerned with Cascading/Deleting when a row is changed. For example, let's say I have a USERS table, with the column 'USERID' as a primary key. Other columns would be 'NAME' and 'EMAIL'. In my users table I have two rows: ["1","Joshua","test@example.com"] and ["2","Mark","mark@example.com"]. Now, let's say I have a table that records a user's favorite film (FAVEFILMS). Columns would be "FAVEID" (auto-increment key), "USERID" (indexed because we will be looking up by userID) and "FILM". In this favefilms table, let's put two rows: ["1","2","Jaws"] and ["2","1","Romancing the Stone"]. Obviously there is a relationship between the USERID in the users table and the USERID in the favefilms table, but what is the purpose of explicitly defining the USERID in the favefilms table as a foreign key? If I'm writing a select statement to find Joshua's favorite film, I would just say "SELECT FILM FROM FAVEFILMS WHERE USERID='1'". If I've indexed the UserID column as a regular index (as should be done with any column that will be used in a WHERE clause), does defining it as a foreign key add anything useful? or does NOT defining it as a foreign key create any harm? Just wondering... I mean, hey I have no problem specifying foreign keys, I was just wondering what the purpose was if I was not interested in CASCADING or DELETING when changes happen to a table.
View Replies !
Can't Create New User In 4.1.18
this is what I am doing: mySQL> GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON store. TO newusername@127.0.0.1 IDENTIFIED BY 'newpassword'; I have tried leaving out , LOCK TABLES ON also just ,LOCK TABLES it keeps saying: 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 'TO newusername@127.0.0.1 IDENTIFIED BY 'newpassword'' at line 1 I am using 4.1.18
View Replies !
Can't Create Database
I'm using ver 5 with winXP. I can access the test database, but can't create a table. I can't create ANY database as I keep getting error 1044 (42000): Access denied for user: ' '@'localhost' to database 'mynewdatabase'.
View Replies !
Cannot Create User
I have a problem with mySql server: I can only run it as root. I use mySql 4.0.15 and phpMyAdmin 2.5.5. on Mac OSX 10.3.2. I run mySql server as root, log in via phpMyAdmin as root and I see there is no user mysql when I click 'Privileges'. When I try to make new user 'MySql' (with minimal privileges) and click 'Go' I get an error 'unknown command'. The corresponding mySql statement is: GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES ON * . * TO "mysql"@ "%"IDENTIFIED BY "*****"WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; #1047 - Unknown command How can I log in as user root and not have Grant privileges??? More importantly, how can I create a user mysql with the normal set of privileges (update, create, alter etc)? I have been using the procedure described on the mysql.com website 'A.4.2 How to Reset a Forgotten Root Password' to reset the password for root.
View Replies !
Create User
I have a problem with mySql server: I can only run it as root. I use mySql 4.0.15 and phpMyAdmin 2.5.5. on Mac OSX 10.3.2. I run mySql server as root, log in via phpMyAdmin as root and I see there is no user mysql when I click 'Privileges'. When I try to make new user 'MySql' (with minimal privileges) and click 'Go' I get an error 'unknown command'. The corresponding mySql statement is: GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES ON * . * TO "mysql"@ "%"IDENTIFIED BY "*****" WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; #1047 - Unknown command How can I log in as user root and not have Grant privileges??? More importantly, how can I create a user mysql with the normal set of privileges (update, create, alter etc)? I have been using the procedure described on the mysql.com website 'A.4.2 How to Reset a Forgotten Root Password' to reset the password for root.
View Replies !
Create A New Database
I would like to create a new database. But something has me wondering. One of the fields "keywords" has to be searchable. Let's suppose it will be a very large database, 20.000 to 50.000 records. Suppose the "keywords" field is limited to 5 keywords. Now what is the best way to do it: - one (1) field called "keywords" and separate the keywords by a comma like this: mp3,download,software,players,music - five (5) fields as follows: keyword1 with data: 'mp3' keyword2 with data: 'download' keyword3 with data: 'software' keyword4 with data: 'players' keyword5 with data: 'music' Which one would be faster? Which one would require the most server resources? What is the best way to do it?
View Replies !
Can't Create A New Thread
I'm getting this message sometimes when I try to connect to my db... do you know why? It works fine most of the time, but once in a while it won't connect. Error 1135: Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug Last time I left the server alone for a couple of hours and then it let me connect... I'm just curious why, and if there's any way I can speed the recovery process and/or keep this error from occuring in the first place.
View Replies !
Create Tables Mac Os X 10.3.5
I recently installed mysql administrator on a mac os X 10.3.5 and am not able to create any table at all in any new created schema. If I select 'create table' from the dropdownlist no popup menu appears . If I select create table selecting the existing db 'mysql' I do get the popup menu to create a new table but get an error while trying to execute query... :s. To be sure it wouldn't be a permission problem I set the rights to 777 but still no go,
View Replies !
Create New DB's
After installing MySQL on a WinXP machine using an Administrator account I cant create new databases. When I go to the Databases tab and right click in there, then I only have the following options: Flush Hosts Flush Logs Flush Tables Flush Treads On another machine I have running MySQL, at the top of that list of 'Create Database' I can connect and use the "test" database thats installed by default but that has no user or password so its a bit of a security worry as its rather easy to get into with phpMyAdmin.
View Replies !
Cannot Create New Records
I am running a multiuser PHP/SQL site. Each user has a table to store records in, populated by PHP forms.This evening I was entering some data into my own account and after inserting a few records the server now refuses to enter more records to that table. When I submit the PHP page that creates the new record, the page completes as normal with no errors but no record is created. Other users and tables can still add records. The server has plenty of storage space. I notice that my table has reached 70 rows. Is this a default maximum for the number of rows in a table? How can I get around this?
View Replies !
Cant Create Database
i am not able to create database using my userid. I am creating databases using a php script where using root user to do the same is not an option. i have create database permissions. i get the same error both from command line and phpmyadmin 2.6.0. I am using mysql 4.1.7 on linux.here is what i get: mysql> create database sid; ERROR 1044 (42000): Access denied for user 'sid'@'localhost' to database 'sid'
View Replies !
|
TRACKER
