Re: [PHP-DB] indexing error - key length not specified
Perhaps I should of spoke more exactly. In MySQL 5.0 you can index these, but the maximum index length is limted by the storage engine. So you have to be specific as to how you index these columns. The deal is that with large column fields making an index of several thousand characters (for example) doesn't help you that much, the index gets as big as the field (comparatively) and the performance gains from having an index is marginal, as I understand it. (an expert is free to jump in at this point) So one might want to analyze why you're indexing this field at all. Perhaps something specialized like a fulltext index is more appropriate. Take a look at this page in the docs: http://dev.mysql.com/doc/refman/5.0/en/indexes.html It explains it somewhat. You can as it mentions specify the index size which the db should take, but you'll be indexing a subset of the actual data with the col_name() style of statement. In my opinion, you might want to take a look at why you want to index such a large column, I don't see a reason myself unless it's something like a fulltext index. I hope that helps, -Micah On 02/09/2007 04:59 PM, John wrote: Micah, You can't use that column type as an index because it's variable length. Makes sense. Make it a varchar or something that's definite to index it. It is possible the value stored can reach up to about 2k characters, which is too long for a varchar type. Any recommendations as to what type to use that wouldn't be variable length, but be able to store strings/values of that size? Thanks! John -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 6:29 PM To: John Pillion Cc: php-db@lists.php.net Subject: Re: [PHP-DB] indexing error - key length not specified You can't use that column type as an index because it's variable length. Make it a varchar or something that's definite to index it. -Micah On 02/09/2007 03:56 PM, John Pillion wrote: I am trying to set an index on a field in my table, but am getting the following error: BLOB column 'ReadBy' used in key specification without a key length The fieldtype is blob, though I get the same error whether blob or text, or the medium and long versions of each. My first though, based on the error, was I needed to set a length to the field - but it won't accept/store any length I give it (because it's a variable length?) I'm using mysql on from 1and1 (hosting provider), though I don't know what version they're running .. any thoughts? As an alternative, how much less efficient would it be to do a ".LIKE '%mystring%'" versus a MATCH..?
RE: [PHP-DB] indexing error - key length not specified
Micah, > You can't use that column type as an index because it's variable length. Makes sense. > Make it a varchar or something that's definite to index it. It is possible the value stored can reach up to about 2k characters, which is too long for a varchar type. Any recommendations as to what type to use that wouldn't be variable length, but be able to store strings/values of that size? Thanks! John -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 6:29 PM To: John Pillion Cc: php-db@lists.php.net Subject: Re: [PHP-DB] indexing error - key length not specified You can't use that column type as an index because it's variable length. Make it a varchar or something that's definite to index it. -Micah On 02/09/2007 03:56 PM, John Pillion wrote: > I am trying to set an index on a field in my table, but am getting the > following error: > > > > BLOB column 'ReadBy' used in key specification without a key length > > > > The fieldtype is blob, though I get the same error whether blob or text, or > the medium and long versions of each. My first though, based on the error, > was I needed to set a length to the field - but it won't accept/store any > length I give it (because it's a variable length?) > > > > I'm using mysql on from 1and1 (hosting provider), though I don't know what > version they're running > > > > .. any thoughts? > > > > > > As an alternative, how much less efficient would it be to do a ".LIKE > '%mystring%'" versus a MATCH..? > > > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] indexing error - key length not specified
You can't use that column type as an index because it's variable length. Make it a varchar or something that's definite to index it. -Micah On 02/09/2007 03:56 PM, John Pillion wrote: I am trying to set an index on a field in my table, but am getting the following error: BLOB column 'ReadBy' used in key specification without a key length The fieldtype is blob, though I get the same error whether blob or text, or the medium and long versions of each. My first though, based on the error, was I needed to set a length to the field - but it won't accept/store any length I give it (because it's a variable length?) I'm using mysql on from 1and1 (hosting provider), though I don't know what version they're running .. any thoughts? As an alternative, how much less efficient would it be to do a ".LIKE '%mystring%'" versus a MATCH..? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] indexing error - key length not specified
I am trying to set an index on a field in my table, but am getting the following error: BLOB column 'ReadBy' used in key specification without a key length The fieldtype is blob, though I get the same error whether blob or text, or the medium and long versions of each. My first though, based on the error, was I needed to set a length to the field - but it won't accept/store any length I give it (because it's a variable length?) I'm using mysql on from 1and1 (hosting provider), though I don't know what version they're running .. any thoughts? As an alternative, how much less efficient would it be to do a ".LIKE '%mystring%'" versus a MATCH..?
Re: [PHP-DB] indexing on existing DB
But, remember - you don't rebuild completely an index - you rather add to it. So, there are three scenarios: 1. Q: You create the table to then send into it lots of data row per row A: create the table first, then build an idex on it once. 2. Q: You have to constantly be adding the data into a table A: create the table with an index and keep adding data to it 3. Q: You add chunks of data into the existing indexed table A: If table is really large, you need a *big* loop to insert one by one and care about that very time - drop index first, insert the data, recreate the index when server is not busy. However, this is rare. Most often you will need to make your SQL smarter to inject data all at once like SELECT INTO. This (supposly) will do indexing only once as it is only one operation. Well, even if mySQL is not THAT relational. -- Maxim Maletsky [EMAIL PROTECTED] "John W. Holmes" <[EMAIL PROTECTED]> wrote... : > > In MySQL, is there any difference between creating an index at table > > creation time, and creating an index on an existing table? Does an > index > > created on an existing table re-index itself after each insert/update, > or > > does it only index itself once - when you create the index? > > In the end, it's the same. Indexes are constantly being built (or added > to) with each insert or update. > > Where it's helpful to create an index after the table is loaded with > data, is if you are loading a bunch of data into a table. Indexes will > slow down INSERTs because it also has to update the index when it > inserts the data. So, it'll be quicker overall to insert all of your > data, and then create an index on the table. This is only applicable if > you're inserting a large amount of data. > > ---John Holmes... > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] indexing on existing DB
> In MySQL, is there any difference between creating an index at table > creation time, and creating an index on an existing table? Does an index > created on an existing table re-index itself after each insert/update, or > does it only index itself once - when you create the index? In the end, it's the same. Indexes are constantly being built (or added to) with each insert or update. Where it's helpful to create an index after the table is loaded with data, is if you are loading a bunch of data into a table. Indexes will slow down INSERTs because it also has to update the index when it inserts the data. So, it'll be quicker overall to insert all of your data, and then create an index on the table. This is only applicable if you're inserting a large amount of data. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] indexing on existing DB
Except when you have a million records, which may take a few seconds to build. :-) Marco - php|architect -- The Monthly Magazine For PHP Professionals Come visit us on the web at http://www.phparch.com! On Fri, 2002-11-08 at 11:12, Maxim Maletsky wrote: > > Index is always re-indexing itself on INSERT/UPDATE. Roughly, I don't > think there is any difference in when you create an index before or > after table is populated. > > > -- > Maxim Maletsky > [EMAIL PROTECTED] > > > > "Jason Vincent" <[EMAIL PROTECTED]> wrote... : > > > (I know this is more of a mySQL question than PHP, but allow me this one if > > you would...) > > > > In MySQL, is there any difference between creating an index at table > > creation time, and creating an index on an existing table? Does an index > > created on an existing table re-index itself after each insert/update, or > > does it only index itself once - when you create the index? > > > > Thanks in advance > > > > Regards, > > > > J > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] indexing on existing DB
Index is always re-indexing itself on INSERT/UPDATE. Roughly, I don't think there is any difference in when you create an index before or after table is populated. -- Maxim Maletsky [EMAIL PROTECTED] "Jason Vincent" <[EMAIL PROTECTED]> wrote... : > (I know this is more of a mySQL question than PHP, but allow me this one if > you would...) > > In MySQL, is there any difference between creating an index at table > creation time, and creating an index on an existing table? Does an index > created on an existing table re-index itself after each insert/update, or > does it only index itself once - when you create the index? > > Thanks in advance > > Regards, > > J > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] indexing on existing DB
(I know this is more of a mySQL question than PHP, but allow me this one if you would...) In MySQL, is there any difference between creating an index at table creation time, and creating an index on an existing table? Does an index created on an existing table re-index itself after each insert/update, or does it only index itself once - when you create the index? Thanks in advance Regards, J
[PHP-DB] indexing text fields in mysql ?
Hi there, I am wondering if it would be anyhow possible to make a search on a textfield faster. Right now the table contains 294000 entries and takes about 40 MB of space. Is there a way to apply a index with a resonable amount of disk space? I am also not so sure if I should use text or smalltext columns as the info may vary between 1 and 1000 chars. Thanx for any help on that, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] indexing
I could be wrong but I seem to remember that Primary keys are indexed automatically. Regardless, smallints and dates really are pretty easy to index. So go for it! Strings introduce a bit of overhead, but I've never heard anyone, nor would I, recommend holding back on indexing because you have too many. Remember, the server only uses the index when it can, or when you explicitly tell it to. Sometimes it will try to use an index and it shouldn't, but there's never really a reason not to use it on numeric fields, which all of yours really are (dates are numeric on the insides). <>< Ryan -Original Message- From: Steve Bradwell [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 9:24 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] indexing Hi all, More of a database question here, my apologies: I've been doing some reading on indexes but I thought I should get an experienced persons input. My question is about "Over Indexing". I have a MySQL MyISAM table that stores inventory transactions, in, out etc. I store numeric fields that are primary indexes in other tables, 2 datetime fields and a qty field, Paul DuBois' MySQL book says that anything used in a where clause or join clause is a candidate for an index. It turns out that 7 out of eight fields fit this description,1 primary key, 4 smallints, and 2 are datetime fields. Should I use all these indexes or is this overkill? Thanks for any input Steve. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] indexing
Hi all, More of a database question here, my apologies: I've been doing some reading on indexes but I thought I should get an experienced persons input. My question is about "Over Indexing". I have a MySQL MyISAM table that stores inventory transactions, in, out etc. I store numeric fields that are primary indexes in other tables, 2 datetime fields and a qty field, Paul DuBois' MySQL book says that anything used in a where clause or join clause is a candidate for an index. It turns out that 7 out of eight fields fit this description,1 primary key, 4 smallints, and 2 are datetime fields. Should I use all these indexes or is this overkill? Thanks for any input Steve. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Indexing help
Columns used in the WHERE clause is the good identifier for which columns to index. You don't want to index every column you select. Typically adding indexes decreases insert performance. The more indexes the slower inserts operate, because the indexes also must be updated. Adding an index to a column which is used in a where clause can dramatically increase table row selection. The database server does not have to scan the full table, instead it can use the index to lookup which rows to return. On small static tables the performance gain can be nominal. Larger tables where there are a lot of rows to check for a match, will see the largest performance gain. Think of a dictionary. You already know the index: words are alphabetically sorted. Just think if it wasn't, you'd have to look at every word to see if you've got a match. Without indexes, that's what the computer has to do. The computer (dbms) picks which index to use based on whats column in the where clause. Order By clauses can also benifit from an index. -Joe ""Steve Brett"" <[EMAIL PROTECTED]> wrote in message 99pjr2$20s$[EMAIL PROTECTED]">news:99pjr2$20s$[EMAIL PROTECTED]... > replies below: > > > ""M. Verheijen"" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Dear reader, > A newbie mysql/php-question here! I've filled a mysql-database with about > 1600 records. All these records contain items which > are on sale on a website. Every row contains an integer defining the > category to which a item belongs. > > At the left of the website there are buttons linked to a php-page which does > a selection like this: > select * from PRODUCTS where SUBCODE = \"$SUBCODE\" > > As you might have guessed, every button is a link in the form of: > > > This all works well, but now my questions! > Is it wise to make an index for the integer column? I assume it is because > it's the row almost all queries are using in there where-statements. What > kind of speed increase can I expect, will it increase the speed of query's > if the workload goes to 2000 queries a hour? What's the best way to make > this index, how does it works. > > >> > i always work on the theory that i index columns that i use in the select > section of a query, or a where. > keys will be indexed automatically (AFAIU). > the speed increase will be dramatic. i used postgres and didn't index a > field (accidently) and then ran queries.then indexed it and the speed > increase is huge. > as for making indexes it depends what tools you use. most tools such as > mysql-admin etcc will let you create indexes by a pointy clicky interface. > true purists might insist on: > > CREATE [UNIQUE] INDEX index_name ON table [USING acc_name] > > from the sql command line. > > <<< > > I've read about the use of indexes in the mysql-manual and phpbuilder.net, > but both are kind of short and don't tell what > you're doing exactly. Anybody of you have some pointers to good webresources > about indexing. > > > i don't think you need to get into what particular type of index it is > (hash, etc) but they are generally a good thing. > << > > Yes, I know a long list of questions ;) I hope that some of you can give me > a clue or two. > > >>> > log lists of questions are a good thing and should be warmly welcomed by > everyone. > <<< > > > With best regards, > Maarten Verheijen > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Indexing help
replies below: ""M. Verheijen"" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Dear reader, A newbie mysql/php-question here! I've filled a mysql-database with about 1600 records. All these records contain items which are on sale on a website. Every row contains an integer defining the category to which a item belongs. At the left of the website there are buttons linked to a php-page which does a selection like this: select * from PRODUCTS where SUBCODE = \"$SUBCODE\" As you might have guessed, every button is a link in the form of: This all works well, but now my questions! Is it wise to make an index for the integer column? I assume it is because it's the row almost all queries are using in there where-statements. What kind of speed increase can I expect, will it increase the speed of query's if the workload goes to 2000 queries a hour? What's the best way to make this index, how does it works. >> i always work on the theory that i index columns that i use in the select section of a query, or a where. keys will be indexed automatically (AFAIU). the speed increase will be dramatic. i used postgres and didn't index a field (accidently) and then ran queries.then indexed it and the speed increase is huge. as for making indexes it depends what tools you use. most tools such as mysql-admin etcc will let you create indexes by a pointy clicky interface. true purists might insist on: CREATE [UNIQUE] INDEX index_name ON table [USING acc_name] from the sql command line. <<< I've read about the use of indexes in the mysql-manual and phpbuilder.net, but both are kind of short and don't tell what you're doing exactly. Anybody of you have some pointers to good webresources about indexing. i don't think you need to get into what particular type of index it is (hash, etc) but they are generally a good thing. << Yes, I know a long list of questions ;) I hope that some of you can give me a clue or two. >>> log lists of questions are a good thing and should be warmly welcomed by everyone. <<< With best regards, Maarten Verheijen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Indexing help
Dear reader, A newbie mysql/php-question here! I've filled a mysql-database with about 1600 records. All these records contain items which are on sale on a website. Every row contains an integer defining the category to which a item belongs. At the left of the website there are buttons linked to a php-page which does a selection like this: select * from PRODUCTS where SUBCODE = \"$SUBCODE\" As you might have guessed, every button is a link in the form of: This all works well, but now my questions! Is it wise to make an index for the integer column? I assume it is because it's the row almost all queries are using in there where-statements. What kind of speed increase can I expect, will it increase the speed of query's if the workload goes to 2000 queries a hour? What's the best way to make this index, how does it works. I've read about the use of indexes in the mysql-manual and phpbuilder.net, but both are kind of short and don't tell what you're doing exactly. Anybody of you have some pointers to good webresources about indexing. Yes, I know a long list of questions ;) I hope that some of you can give me a clue or two. With best regards, Maarten Verheijen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
