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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
Index, Then Query On Only Part Of The Index
Lets say we have a fulltext index on... column 1, column 2, and column 3 If we do a select statement matching only on column 2 and column 3 will the index still be used effectively? Do we then need a second index only on column 2, and column 3? Would it be smart to simply create indexes on all of the following? column 1 column 2 column 3 column 1, 2, 3 column 2, 3 ?
View Replies !
View Related
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 !
View Related
Index To Hit First
I have a multi-million row table with three indexes in MySQL-5.0.15. These indexes have the following number of distinct values: date 415 block 100000 scan 45 If I'm doing a query on this table in what order should I hit these indexes? The largest first, to get rid of the most possibilities right away? Or the smallest first, because it has the fewest number of rows to scan to make the cut? At some level both seem to make sense, but I'm doing smallest to largest. Is this the most efficient? Or is this a case of just letting the query optimizer choose? I don't yet have a compound index on these fields.
View Replies !
View Related
Why LIKE Not Using INDEX?
I have a table with an intger column called ID. I have an index on it. Now I want to get a result with all the rows whos ID values start with lets say 12....i.e. I want all the IDs with data - 12 123 1234 ........... select * from tablename where id like '123%' It is returning me correct results...but the query does not seem to be using the INDEX? My question is ... does MySQL not uses INDEX when we execute a LIKE command on a NUMERIC data.... __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw
View Replies !
View Related
An Index
I've a mysql database running with a lot of documentation files. Now I wonder how it is possible to generate an index file (in plain text) of this files sorted by description for publishing on my website.
View Replies !
View Related
How To Index
I have several queries showing up in the slow query log, similar to: SELECT COUNT(id) FROM stats WHERE (int1 + int2 + int3 > 1000); I have indexes on each of the three fields, int1, int2, and int3 (These are stored as integers)
View Replies !
View Related
INDEX Became KEY
i've created table manually and set INDEX on the one of my columns, then i exported my table with phpmyadmin and the INDEX became KEY in the output. are they similar?
View Replies !
View Related
Index Or Not Index
I have 3 questions: 1. Should I index all columns that are used in where statements or LIKE statements? 2. Should I index date/datetime field if I use it in queries like "WHERE created_at > '2008-05-10'" or "ORDER BY created_at DESC" 3. In which cases are multi-column indexes used? Could you give me a link to example so I can have a little practice?
View Replies !
View Related
Index 7
The following is part of my script that does not work, the first line is what i think is causing the problem. going by the error returned. i have had this before and looked at my other script to work it out again, but can not see anything wrong $res = mysql_query("SELECT * FROM list WHERE email='$findstring' LIMIT 1") or die(mysql_error()); $name = mysql_result($res, 0, "name") or die(mysql_error());
View Replies !
View Related
Tb1.Name=tb2.Name Not Use Index On Name
I did the following query. the EXPLAIN result showed 'NULL' in the 'key' colume, meaning that NONE of indexes have been used. However, indexes have been created on Name fields for both tb1 and tb2. I wonder why MySQL does not use the indexes to speed up the query. Is there any way to make the query faster? Code: EXPLAIN SELECT tb1.Name FROM tb1, tb2 WHERE tb1.Name = tb2.Name;
View Replies !
View Related
Getting The Index
I've got a table with an auto-incrementing index column (named "ID") which is also the primary key. After inserting a new record, is there an efficient way to get the index at which it was inserted? Presumably I could do "select max(id) from tablename", or "select ID from tablename order by ID desc limit 1". (Which one would be faster, BTW?) But that's assuming that no other records are inserted between the insert and the query. Is there a common method of doing this?
View Replies !
View Related
Index Is Not Used :-(
It is a simple query and my index is not used :-( Index is "zadnj"e and I indexed "zadnjidatum, zadnjiuporabnik" together. Query is EXPLAIN SELECT r.ID, r.imerecepta, r.zadnjidatum, r.zadnjiuporabnik, r.stmnenj, o.ime, o.uporabnik FROM recepti r INNER JOIN obiskovalci o ON r.zadnjiuporabnik = o.ID ORDER BY r.zadnjidatum, r.zadnjiuporabnik Explain says: id select_type table type possible_keys key key_len ref rows Extra 1SIMPLErALLzadnjeNULLNULLNULL6788Using filesort 1SIMPLEoeq_refPRIMARYPRIMARY4r.zadnjiuporabnik1 What am I doing wrong? I used another similar query and I didn't get using filesort and not using index.
View Replies !
View Related
Index Help
I deleted a fulltext index on one of my mysql tables, which then broke my php frontend. I recreated the index, but the app is still broken. How can making a change to a secondary index, or in this case a fulltext index, adversly affect my application?
View Replies !
View Related
Why Don't Use Index
I have a table Test with three fields: id int(11) not null auto_increment; name varchar(15) not null; age tinyint(4) not null. [1]select * from Test where id like '%1'; [2]select * from Test where id like '1%'; It's said that query[1] cannot use index, but query[2] can do. But with my tests, both of them didn't use index. My tests were very easy, I used "log_queries_not_using_indexes" option. The relevant statements in my.ini as follows, log-slow-queries=slow-query.log long_query_time=10 log_queries_not_using_indexes I just had a little data, so both queries weren't "long time query".
View Replies !
View Related
How Best To Index This?
I have a table called profiles which I want to run this query on: SELECT * FROM profiles WHERE APPROVED = 1 AND HIDDEN = 0 ORDER BY LAST_EDITED DESC LIMIT 1000,10 I've tried every combination of index with LAST_EDITED, APPROVED and HIDDEN but it always seems to just use the index on LAST_EDITED only, is there anyway to make an index which would be better for it?
View Replies !
View Related
Using Index
I had a 1.6 Gb table (innodb) with 12 indexes, which I later reduced into a 250Mb table with 2 indexes. The problem is that the index seems to be bigger in size than the table (270Mb). What am I doing wrong? How can I make the index smaller?
View Replies !
View Related
2 Index
i've been looking at some of my old databases with a view to optimising them as i'm totally self-taught and never realised loads of stuff like indexing when i created the databases! i'm using MySQL 3.23.58, all tables using MyISAM. on one of my tables (orders) i have built an index on the customer ID field but when i view it in phpMyAdmin this index has the same cardinality as the PRIMARY index (i.e. order number). now i know for sure that some customers have ordered more than once, so shouldn't the cardinality of the customer INDEX be lower than the PRIMARY INDEX? the customer ID field is type MEDIUMINT(8). on another database, in another table, i have 4 text columns for varying degrees of scientific calculations (species, genus etc). i have built a FULLTEXT index across the 4 columns as i want non-scientific people (who don't understand the difference between genus, species etc) to be able to query the database. now i don't seem to be able to add any other INDEXes on this table. i want to index the genus column by it's first letter so i can browse the table by letter. this works fine without the FULLTEXT INDEX but not with. is this right that a FULLTEXT INDEX can be the only INDEX (other than the PRIMARY) on a table.
View Replies !
View Related
WHERE..IN Not Using Index
Here is the query: SELECT `vkey`, `data` FROM `registry` WHERE `vkey` IN('prioritycache','statuscache','staffcache','departmentcache','slaplancache','slaschedulecache', 'escalationrulecache','attachmenttypescache','ticketcountcache','ticketgridcache','ticketviewcache', 'queuecache','instaalertcache','ticketlabelcache','labelcountcache','gridcache','groupsettingcache', 'commentcache','staffassigncache','groupassigncache','tgroupassigncache','settingscache','tgroupcache','languagecache'); An INDEX exists on the `vkey` column. I've already OPTIMIZE, and ANALYZE this table. But when I run the query with EXPLAIN, no indexs are used. There are 58 rows in this table. If I reduce the number of options in the IN portion down to like, 5 or 6, the index is used. But if you increase past 5 or 6, the INDEX is no longer used.
View Replies !
View Related
Should I Use Index?
I have table Files(id,name,type) . Type is INT, the possible values are(1,2,3,4,5). Often i execute query which gives me files of given type e.g.: SELECT id,name FROM files where type = 1
View Replies !
View Related
What Should I Index In This?
If this is my query and I'm calculating distance between users what should I index in the database? This query works really fast in my database of 10,000 users when I have state='xx' in the where clause. But if I don't it goes crazy and takes minutes! I'd love to sort by distance but that takes too long as well. Any ideas on what to index? SELECT SQL_CALC_FOUND_ROWS users_info.username,users_info.id ,CONCAT(zipcodes.city,', ', zipcodes.state) AS user_area ,DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthdate)), '%Y')+0 AS user_age ,user_greeting.greeting , (3958 * 3.1415926 * sqrt((zipcodes.latitude - -111.978898) * (zipcodes.latitude - -111.978898) + cos(zipcodes.latitude / 57.29578) * cos(-111.978898 / 57.29578) * (zipcodes.longitude - +40.606125) * (zipcodes.longitude - +40.606125)) / 180) as distance FROM users_info LEFT JOIN user_greeting ON users_info.id = user_greeting.user_id INNER JOIN zipcodes ON users_info.zip = zipcodes.zipcode WHERE users_info.birthdate<��-02-16 06:34:03' AND users_info.birthdate>��-02-18 06:34:04' AND users_info.state='CA' AND users_info.sex = Ƈ' HAVING distance<ཕ' LIMIT 0,10
View Replies !
View Related
Index When Using OR?
Let's say I have a private message table like this: Code: SELECT id , uid1 , uid2 , msg , msgdate FROM user_pm WHERE uid1 = 999 OR uid2 = 999 ORDER msgdate DESC LIMIT 10 I want to fetch all PM sent or received for a user. uid1 will be sender, uid2 receiver. Can I put up an INDEX for this? I've been reading some and it seems OR can't use indexes, or am I wrong? I tried setting up a composite index called uid1ui2 for (uid1,uid2) instead of indexes for uid1 and uid2. Doesn't really seem to show any difference in EXPLAIN. using AND seems to work for composite (which might be what it's supposed to do). I have a similar setup which will need to be searched through on every page refresh (not PM) so for that query I'd want to use indexes. Should I just do two separate queries, WHERE uid1=999 and another one with uid2=999 (with an index on each column), and then do some sorting in php (will probably need to set a higher LIMIT to actually get *close to* everything), or is there a better way?
View Replies !
View Related
Never Update Index
How do I make sure that index is never updated to disk. This is the only way I can keep my machine from running hot. I understand that if there is a sudden reset of the machine, all the indecis need to be rebuilt.
View Replies !
View Related
MySQL Not Using Index
Can someone explain to me why this simple query will not use an index on the field confirm_date? select * from comments where confirm_date != 0 confirm_date is an integer, and I have a regular index on it. When I use EXPLAIN it shows all 1233 rows being searched with NULL for the possible keys.
View Replies !
View Related
Index Clarification
I have a table with two indexes: indexA table(A), and indexB table(B), and I create indexAB table(A, B) it produces two indexes, A and B. Can I drop the indexes created earlier on those individual fields and have mysql use the two indexes created by the create indexAB command?
View Replies !
View Related
I Need To Index The 'once' Word
I am working with mysql server 5.0 an I have an Index in a database table, but I need to index the word 'once'. My index it's not indexing it because once is a number... How can I configure Mysql or the index to index this word? My index is Fulltext type.
View Replies !
View Related
Index By END Of String
for example, if you have a list of email addresses, searching for something like '%@yahoo.com' would need to do a full table scan. why doesn't mysql offer a feature where it indexes by the END of the string, so we can do these types of searches efficiently? an index for email addresses of length 3 would only contain the last 3 chars, but it would still be a useful index type.
View Replies !
View Related
Word Index
I would like to know if there is a way of building an index on all the words of a column.We want to have an alphabetical search on all the words of a description of a product.So, how I treat this index when I change a part of the content of this specific column where I want a word index
View Replies !
View Related
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 !
View Related
Primary Key == Index
when I create a table, I always make the first column my unique-id, and make this primary key auto-increment. Does this mean that the table will be indexed by mysql on this key - or do I need to explicitly do this too? e.g. create table note ( note_id INTEGER(9) AUTO_INCREMENT PRIMARY KEY, mytext varchar(255) ); So do I need to add something to get mysql indexing on the primary key?
View Replies !
View Related
(primary Key == Index) ?
when I create a table, I always make the first column my unique-id, and make this primary key auto-increment. Does this mean that the table will be indexed by mysql on this key - or do I need to explicitly do this too? e.g. create table note ( note_id INTEGER(9) AUTO_INCREMENT PRIMARY KEY, mytext varchar(255) ); So do I need to add something to get mysql indexing on the primary key?
View Replies !
View Related
|
TRACKER
