Re: Index question
- Original Message - From: Tompkins Neil neil.tompk...@googlemail.com Thanks for the information. One final question in what ways should we use EXPLAIN EXTENDED statement to help improve our query performance. Explain is your friend. You should listen to it :-) It gives a nice idea of how the database interprets your query, so you can see where the bottlenecks are, for example what bits don't use indices or cause file sorts. The exact interpretation of it is an art, though, and there are many subtleties you only get by experience and reading documentation. It is not something that is quickly explained; it requires a good knowledge of how a database works on the inside. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Index question
If you have a table with columns A B, and might do a where on A or B, or an order by A, B, would single column indexes on A and B suffice or would performance on the order by query be improved by an index on A,B? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
- Original Message - From: Alex Schaft al...@quicksoftware.co.za If you have a table with columns A B, and might do a where on A or B, or an order by A, B, would single column indexes on A and B suffice or would performance on the order by query be improved by an index on A,B? Depends on usage :-) key (a, b) is good for where a=.. or where a=.. and b=.. key (b, a) is good for where b=.. or where b=.. and a=.. (note that the sequence of a and b in the where clause is not important) key (a), key (b) is good for where a=.. or where b=.. but will only use one index for where a=.. and b=... I think work is ongoing on having the parser use multiple indices, but I'm not sure where that's at. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
Just to clarify having key indexes of (a,b) or (b,a) have no difference ? On 11 Oct 2011, at 09:36, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Alex Schaft al...@quicksoftware.co.za If you have a table with columns A B, and might do a where on A or B, or an order by A, B, would single column indexes on A and B suffice or would performance on the order by query be improved by an index on A,B? Depends on usage :-) key (a, b) is good for where a=.. or where a=.. and b=.. key (b, a) is good for where b=.. or where b=.. and a=.. (note that the sequence of a and b in the where clause is not important) key (a), key (b) is good for where a=.. or where b=.. but will only use one index for where a=.. and b=... I think work is ongoing on having the parser use multiple indices, but I'm not sure where that's at. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
The difference is based on the relative frequency of queries where a is important vs. where b is important. Either way, what will happen is that the index scan will isolate the first item mentioned, then scan the result set to isolate the second term. e.g. SELECT * FROM someTable WERE a = someValue and b = someOtherValue Step one isolates the matching a values. Step two walks through that resultset and examines each value of b. Since the values of b are already in memory, all it has to do is examine the index keys to find the matches of b. No additional disk read is required. Arthur
Re: Index question
Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and 'b' in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan as you don't know 'a', likewise searching for 'a' in an index
(b,a) requires a full scan. See it as looking through a phonebook trying to
locate someone by first- rather then lastname. It's in there, just not easily
accessible.
However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with
only an index on (a).
Johan was trying to explain this distinction:
- index (a,b) is good for searches on ONLY a or BOTH a b, but bad for ONLY
b
- index (b,a) is good for searches on ONLY b or BOTH a b, but bad for ONLY
a
- index (a) index (b) is good for searches on ONLY b or ONLY a, and is
suboptimal for searching for BOTH a,b (although, faster then no index, but the
query optimizer has to choose which index to use, can't use both).
--
Rik Wasmus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
On 2011/10/11 02:22 PM, Rik Wasmus wrote:
Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and 'b' in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan as you don't know 'a', likewise searching for 'a' in an index
(b,a) requires a full scan. See it as looking through a phonebook trying to
locate someone by first- rather then lastname. It's in there, just not easily
accessible.
However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with
only an index on (a).
Johan was trying to explain this distinction:
- index (a,b) is good for searches on ONLY a or BOTH a b, but bad for ONLY
b
- index (b,a) is good for searches on ONLY b or BOTH a b, but bad for ONLY
a
- index (a) index (b) is good for searches on ONLY b or ONLY a, and is
suboptimal for searching for BOTH a,b (although, faster then no index, but the
query optimizer has to choose which index to use, can't use both).
Next question. If you have the two separate indexes and then do two
queries, one for a and one for b. If you then get a list of unique id's
of both, would it be faster to create an intersection yourself rather
than have the server do the legwork?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
On 2011/10/11 02:30 PM, Alex Schaft wrote:
On 2011/10/11 02:22 PM, Rik Wasmus wrote:
Just to clarify having key indexes of (a,b) or (b,a) have no
difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and
'b' in the
second one. Looking for anything that matches 'b' for an index (a,b)
requires
a full scan as you don't know 'a', likewise searching for 'a' in an
index
(b,a) requires a full scan. See it as looking through a phonebook
trying to
locate someone by first- rather then lastname. It's in there, just
not easily
accessible.
However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b)
then with
only an index on (a).
Johan was trying to explain this distinction:
- index (a,b) is good for searches on ONLY a or BOTH a b, but bad
for ONLY
b
- index (b,a) is good for searches on ONLY b or BOTH a b, but bad
for ONLY
a
- index (a) index (b) is good for searches on ONLY b or ONLY a,
and is
suboptimal for searching for BOTH a,b (although, faster then no
index, but the
query optimizer has to choose which index to use, can't use both).
Next question. If you have the two separate indexes and then do two
queries, one for a and one for b. If you then get a list of unique
id's of both, would it be faster to create an intersection yourself
rather than have the server do the legwork?
Then there's index merge optimizations too I suppose
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
Next question. If you have the two separate indexes and then do two queries, one for a and one for b. If you then get a list of unique id's of both, would it be faster to create an intersection yourself rather than have the server do the legwork? If you only have 2 unrelated indexes on a b, it depends on the data, the distribution of values, etc. No single answer here, test with your data and you'll have the results. If you need it often, I'd go for the combined index let MySQL do the work, which is probably fastest. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) ? Or is the decision based on the query response time ? On 11 Oct 2011, at 13:40, Rik Wasmus r...@grib.nl wrote: Next question. If you have the two separate indexes and then do two queries, one for a and one for b. If you then get a list of unique id's of both, would it be faster to create an intersection yourself rather than have the server do the legwork? If you only have 2 unrelated indexes on a b, it depends on the data, the distribution of values, etc. No single answer here, test with your data and you'll have the results. If you need it often, I'd go for the combined index let MySQL do the work, which is probably fastest. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) ? Or is the decision based on the query response time ? Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
- Original Message - From: Rik Wasmus r...@grib.nl Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. I'm trying to think of a scenario where index(a) would be beneficial in the presence of index(a,b). If both are available, and all else being equal, it's likely that the parser will pick the simplest index; but I can't see it having a major impact. Any full prefix of a combined index may be used; so afaik a separate index on any full prefix is a waste of diskspace and cycles. The net conclusion, Neil, is that you actually have to know what you're doing :-) Take the time to read the online documentation on mysql.com, it's pretty good. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
Thanks for the information. One final question in what ways should we use EXPLAIN EXTENDED statement to help improve our query performance. On Tue, Oct 11, 2011 at 2:51 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Rik Wasmus r...@grib.nl Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. I'm trying to think of a scenario where index(a) would be beneficial in the presence of index(a,b). If both are available, and all else being equal, it's likely that the parser will pick the simplest index; but I can't see it having a major impact. Any full prefix of a combined index may be used; so afaik a separate index on any full prefix is a waste of diskspace and cycles. The net conclusion, Neil, is that you actually have to know what you're doing :-) Take the time to read the online documentation on mysql.com, it's pretty good. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
[Q] FULLTEXT index question
Can one make a composite index with FULLTEXT for one column and standard indexing on another? For instance we have a table CREATE TABLE OurData ( TheText TEXT, TheLanguageID INTEGER ); We have a FULLTEXT index on TheText, but want to be able to do searches on TheText AND TheLanguageID. So, an index like ( FULLTEXT TheText, TheLanguageID ) would be nice. Is this possible? Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index, unique index question
A composite index on both columns may be used by queries involving either both columns, or the first column in the index. http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html So, an index on (receiver_id, sender_id) may be used by predicates on both columns or receiver_id alone, but never sender_id alone. (Or I should never say never, since mysql could in the future implement an index scan on the secondary column if the first has very few distinct values). Since James only uses the predicates alone and never combined, I would also suggest a secondary index on (sender_id). unique index (receiver_id, sender_id) index (sender_id) /Kristian Quoting Ananda Kumar [EMAIL PROTECTED]: Hi James, Since your queries have both receiver_id and sender_id in the where condition and u want this to be unique, just create one combined unique index on both these columns. Do this at db level will give you much better options, performance, rather than doing at code level, which might involve quite bit of coding and will slow down the performance of the app. If you create individual index and combined index, there will be huge performance degradation as there would be unnecessary index over heads. regards anandkl On 8/14/07, James Tu [EMAIL PROTECTED] wrote: I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: index, unique index question
Of course, since James said he will never search for a record matching receiver_ID AND sender_ID, it would be more efficient to simply create one index for each of the columns. -Noah -Original Message- From: Kristian Myllymäki [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 14, 2007 3:50 AM To: Ananda Kumar Cc: James Tu; MySQL List Subject: Re: index, unique index question A composite index on both columns may be used by queries involving either both columns, or the first column in the index. http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html So, an index on (receiver_id, sender_id) may be used by predicates on both columns or receiver_id alone, but never sender_id alone. (Or I should never say never, since mysql could in the future implement an index scan on the secondary column if the first has very few distinct values). Since James only uses the predicates alone and never combined, I would also suggest a secondary index on (sender_id). unique index (receiver_id, sender_id) index (sender_id) /Kristian Quoting Ananda Kumar [EMAIL PROTECTED]: Hi James, Since your queries have both receiver_id and sender_id in the where condition and u want this to be unique, just create one combined unique index on both these columns. Do this at db level will give you much better options, performance, rather than doing at code level, which might involve quite bit of coding and will slow down the performance of the app. If you create individual index and combined index, there will be huge performance degradation as there would be unnecessary index over heads. regards anandkl On 8/14/07, James Tu [EMAIL PROTECTED] wrote: I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index, unique index question
Of course, since James said he will never search for a record matching receiver_ID AND sender_ID, it would be more efficient to simply create one index for each of the columns. Then again, his question isn't really about indices (to speed up searches and what not), but about constraints, which, from what I read, he needs. A compound unique constraint is what he needs. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -Noah -Original Message- From: Kristian Myllymäki [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 14, 2007 3:50 AM To: Ananda Kumar Cc: James Tu; MySQL List Subject: Re: index, unique index question A composite index on both columns may be used by queries involving either both columns, or the first column in the index. http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html So, an index on (receiver_id, sender_id) may be used by predicates on both columns or receiver_id alone, but never sender_id alone. (Or I should never say never, since mysql could in the future implement an index scan on the secondary column if the first has very few distinct values). Since James only uses the predicates alone and never combined, I would also suggest a secondary index on (sender_id). unique index (receiver_id, sender_id) index (sender_id) /Kristian Quoting Ananda Kumar [EMAIL PROTECTED]: Hi James, Since your queries have both receiver_id and sender_id in the where condition and u want this to be unique, just create one combined unique index on both these columns. Do this at db level will give you much better options, performance, rather than doing at code level, which might involve quite bit of coding and will slow down the performance of the app. If you create individual index and combined index, there will be huge performance degradation as there would be unnecessary index over heads. regards anandkl On 8/14/07, James Tu [EMAIL PROTECTED] wrote: I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index, unique index question
very true, to have unique constraint on both columns, he needs to create a composite index using both columns. regards anandkl On 8/14/07, Martijn Tonies [EMAIL PROTECTED] wrote: Of course, since James said he will never search for a record matching receiver_ID AND sender_ID, it would be more efficient to simply create one index for each of the columns. Then again, his question isn't really about indices (to speed up searches and what not), but about constraints, which, from what I read, he needs. A compound unique constraint is what he needs. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -Noah -Original Message- From: Kristian Myllymäki [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 14, 2007 3:50 AM To: Ananda Kumar Cc: James Tu; MySQL List Subject: Re: index, unique index question A composite index on both columns may be used by queries involving either both columns, or the first column in the index. http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html So, an index on (receiver_id, sender_id) may be used by predicates on both columns or receiver_id alone, but never sender_id alone. (Or I should never say never, since mysql could in the future implement an index scan on the secondary column if the first has very few distinct values). Since James only uses the predicates alone and never combined, I would also suggest a secondary index on (sender_id). unique index (receiver_id, sender_id) index (sender_id) /Kristian Quoting Ananda Kumar [EMAIL PROTECTED]: Hi James, Since your queries have both receiver_id and sender_id in the where condition and u want this to be unique, just create one combined unique index on both these columns. Do this at db level will give you much better options, performance, rather than doing at code level, which might involve quite bit of coding and will slow down the performance of the app. If you create individual index and combined index, there will be huge performance degradation as there would be unnecessary index over heads. regards anandkl On 8/14/07, James Tu [EMAIL PROTECTED] wrote: I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index, unique index question
I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index, unique index question
Hi James, Since your queries have both receiver_id and sender_id in the where condition and u want this to be unique, just create one combined unique index on both these columns. Do this at db level will give you much better options, performance, rather than doing at code level, which might involve quite bit of coding and will slow down the performance of the app. If you create individual index and combined index, there will be huge performance degradation as there would be unnecessary index over heads. regards anandkl On 8/14/07, James Tu [EMAIL PROTECTED] wrote: I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question
If I have 2 large tables A and B and I need to join them: select * from A,B where A.col1=B.col1 If A.col1 is an index and B.col1 is not, if I create an index on B.col1 will this index be used? In other words how are indexes used on table joins? Thanks John Mancuso Linux Administrator/MySQL DBA IT Infrastructure American Home Mortgage w: 631-622-6382 c: 516-652-2475
Re: index question
John Mancuso wrote: If I have 2 large tables A and B and I need to join them: select * from A,B where A.col1=B.col1 If A.col1 is an index and B.col1 is not, if I create an index on B.col1 will this index be used? In other words how are indexes used on table joins? Thanks John Mancuso Linux Administrator/MySQL DBA IT Infrastructure American Home Mortgage w: 631-622-6382 c: 516-652-2475 Use explain, and find out. MySQL may reverse the two and join A to B inorder to use A's index. Since there is no WHERE clause, only one of the tables needs an index. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Run explain select * from A,B where A.col1=B.col1; The explain plan for your query will tell you what indexes are chosen. If your explain plan says what you do not like, definitely add an index on col1 in B. Make sure you run OPTIMIZE TABLE on both tables. The, run explain select * from A,B where A.col1=B.col1; again See what the explain plan says afterwards. Give it a try !!! - Original Message - From: John Mancuso [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 21, 2007 10:07:39 AM (GMT-0500) America/New_York Subject: index question If I have 2 large tables A and B and I need to join them: select * from A,B where A.col1=B.col1 If A.col1 is an index and B.col1 is not, if I create an index on B.col1 will this index be used? In other words how are indexes used on table joins? Thanks John Mancuso Linux Administrator/MySQL DBA IT Infrastructure American Home Mortgage w: 631-622-6382 c: 516-652-2475 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date index question
Hello. Add composite index (tValidFrom, tValidTo) and use constant or variable instead of now(). Force MySQL to use this composite index. Mattias Håkansson wrote: Hello People, I have some indexing problem on using the fieldtype 'date' as restriction in a query. I use MySQL Server version: 4.0.20 The table I have consists of roughly over 200.000 rows about 37 fields and it looks sort of like this: mysql desc the_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | theID | int(11) | | PRI | NULL| auto_increment | | contract | varchar(20) | | MUL | || ... some varchars ... | rate1 | double(10,2) | | | 0.00 || ... twenty other doubles ... | routing| varchar(100) | | | || | cNotes | text | | | || | tValidfrom | date | | MUL | -00-00 || | tValidto | date | | MUL | -00-00 || | iStatus| int(11) | | | 0 || | iEnteredby | int(11) | | | 0 || | tEntered | datetime | | | -00-00 00:00:00 || | iUpdatedby | int(11) | | | 0 || | tUpdated | datetime | | | -00-00 00:00:00 || ++--+--+-+-++ 37 rows in set (0.00 sec) I then run this to create the index I want. mysql CREATE INDEX IX_test ON the_table(tValidfrom,tValidto); Query OK, 204657 rows affected (1 min 6.08 sec) Records: 204657 Duplicates: 0 Warnings: 0 The created index shows this info: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 1 Column_name: tValidfrom Collation: A Cardinality: 75 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 2 Column_name: tValidto Collation: A Cardinality: 131 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 27 rows in set (0.00 sec) So now I want to take advantage of this index, but my query is still slow: mysql SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); | USBDA | | USIND | | USHSV | +-+ 402 rows in set (2.80 sec) Then I do an explain to try to find out if it is using my index. mysql explain SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now() \G; *** 1. row *** table: the_table type: ALL possible_keys: IX_test key: NULL key_len: NULL ref: NULL rows: 204657 Extra: Using where; Using temporary 1 row in set (0.00 sec) So it's not even using my index =( And if I force it: mysql SELECT DISTINCT cOrigin from the_table FORCE INDEX (IX_test) WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); . . | IDPJG | | JPSHI | | INICD | | CNJIU | | USHSV | +-+ 402 rows in set (4.27 sec) It is even slower. I have done a check table, analyze table etc. If you have any suggestions please let me know, thanks for your precious time! Best Regards, Mattias -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date index question
Hello People, I have some indexing problem on using the fieldtype 'date' as restriction in a query. I use MySQL Server version: 4.0.20 The table I have consists of roughly over 200.000 rows about 37 fields and it looks sort of like this: mysql desc the_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | theID | int(11) | | PRI | NULL| auto_increment | | contract | varchar(20) | | MUL | || ... some varchars ... | rate1 | double(10,2) | | | 0.00 || ... twenty other doubles ... | routing| varchar(100) | | | || | cNotes | text | | | || | tValidfrom | date | | MUL | -00-00 || | tValidto | date | | MUL | -00-00 || | iStatus| int(11) | | | 0 || | iEnteredby | int(11) | | | 0 || | tEntered | datetime | | | -00-00 00:00:00 || | iUpdatedby | int(11) | | | 0 || | tUpdated | datetime | | | -00-00 00:00:00 || ++--+--+-+-++ 37 rows in set (0.00 sec) I then run this to create the index I want. mysql CREATE INDEX IX_test ON the_table(tValidfrom,tValidto); Query OK, 204657 rows affected (1 min 6.08 sec) Records: 204657 Duplicates: 0 Warnings: 0 The created index shows this info: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 1 Column_name: tValidfrom Collation: A Cardinality: 75 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Table: the_table Non_unique: 1 Key_name: IX_test Seq_in_index: 2 Column_name: tValidto Collation: A Cardinality: 131 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 27 rows in set (0.00 sec) So now I want to take advantage of this index, but my query is still slow: mysql SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); | USBDA | | USIND | | USHSV | +-+ 402 rows in set (2.80 sec) Then I do an explain to try to find out if it is using my index. mysql explain SELECT DISTINCT cOrigin from the_table WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now() \G; *** 1. row *** table: the_table type: ALL possible_keys: IX_test key: NULL key_len: NULL ref: NULL rows: 204657 Extra: Using where; Using temporary 1 row in set (0.00 sec) So it's not even using my index =( And if I force it: mysql SELECT DISTINCT cOrigin from the_table FORCE INDEX (IX_test) WHERE the_table.tValidFrom = now() AND the_table.tValidTo = now(); . . | IDPJG | | JPSHI | | INICD | | CNJIU | | USHSV | +-+ 402 rows in set (4.27 sec) It is even slower. I have done a check table, analyze table etc. If you have any suggestions please let me know, thanks for your precious time! Best Regards, Mattias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index question
Howdy all, I've noticed some strange behavior with the way that mysql is choosing indexes with a particular query I'm trying to optimize. First off, I'm using MySQL 4.0.24 on MAC OSX. I've got a table that I'm searching on based upon a set of preferences. From one query to the next the set of preferences may change (as well as the values of the preferences). However, there are a couple of basic preferences that all queries will have, so I created a composite index on that set of preferenes (with the least selective pref being the left most column in the index and getting more restrictive going to the right). I also have another index on the leftmost column mentioned above because that field is a FK and MySQL wouldn't let me use the comp index for the FK. So, there are times when I actually want the single column index to be used and other times the composite key, based on how broad the preferences are. So far so good. I added another index, to see if I could speed things up even more and the query performance took a nose dive (about a factor of 7 worse). When I ran the explain I noticed that mysql changed the index that it was using, but not to the new index (the third one). Adding a fourth index made mysql select the orginal index and performance was restored. My question is, why is mysql choosing differet indexes based on the presence of these new indices (that it chooses not to use in place to the old indices)? I got things back on track by just adding and removing indices until things were working the way that I wanted them to, but it seems really strange. Thanks, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index Question in MyISAM
I have the following table:
CREATE TABLE `Article_Search` (
`ArticleID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`ArticleID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
This table has several million rows, but I only want to search a subset of
the table. IE:
SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('rubber
duckies' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018, 1452,
91)
The reason I'm specifying a set of ArticleIDs is that I know any hits are
going to be within those articles. So the presence of the IN() clause is
purely there for performance. However, an explain on this Statement shows
that it is using the Full-Text index. Is mysql text-searching the entire
table under the hood, or does it use the PK to reduce the dataset before the
text-search.
Thanks again!
-Dan
Re: Index Question in MyISAM
Dan Salzer [EMAIL PROTECTED] wrote on 16/05/2005 14:36:41:
I have the following table:
CREATE TABLE `Article_Search` (
`ArticleID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`ArticleID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
This table has several million rows, but I only want to search a subset
of
the table. IE:
SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('rubber
duckies' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018,
1452,
91)
The reason I'm specifying a set of ArticleIDs is that I know any hits
are
going to be within those articles. So the presence of the IN() clause is
purely there for performance. However, an explain on this Statement
shows
that it is using the Full-Text index. Is mysql text-searching the entire
table under the hood, or does it use the PK to reduce the dataset before
the
text-search.
MySQL can only use one index at a time. So if it used the ArticleID index
and your IN clkause as the primary index, it would be reduced to doing the
MATCH() the hard way, line by line, in the articles returned by the IN
clause.
On the other hand, you know that the only articles which contain the words
that you specify, it will be doiing a relatively fast lookup in the
FULLTEXT index to get the same set of IDs that you are feeding it, or an
even smaller one (because some even of those will not contained in the
hits). the only case where the simply doing the FUULTEXT search would not
be as fast as you quote would be when one of the separate words rubber
or duckies has a very large number of hits but the phrase does not.
In sum, I wouldn't bother with this optimisation unless your search truens
out in practice to be slow.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
*snip* Yes. B-tree is always balanced: http://www.nist.gov/dads/HTML/btree.html Regards, Sergei *snip* You are right, B+Trees are always balanced but When you insert in increasing order all your inserts will be to the last leaf of the B+tree. This means that you can get some concurrency problems when updating the index since it is always the same part of the index that needs to be locked. I'm not quite sure how MySQL does it locking, but if it locks only the index leafs that it is updating this kind of insertion will give poor performance compared to random insertion. Best regards, Mads -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
From: Sergei Golubchik [EMAIL PROTECTED] But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. Hi again, does the key page size differ depending on the type of the column (BIGINT, INT, etc)? Is there any way I can work out the key page size, or configure it? Cheers, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Hi Gary, Yeah... we thought about those. Sergei said: you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. So say key base is 200, log 200 (10^9) = 3.91 Splitting it into 10 smaller tables would make log 200 (10^9) = 3.47, which isn't a huge amount of difference I guess. Still, worth testing to see how it performs in practice I guess. Thanks for the tip :) Cheers, -Phil - Original Message - From: Gary Richardson [EMAIL PROTECTED] To: Phil Bitis [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 21, 2004 2:45 AM Subject: Re: B-tree index question If you are using MyISAM tables, have you thought about using MERGE tables instead? You could partition your table into several smaller tables. I don't know how the performance would be on a billion record table, but from my understanding it would shrink your index down. http://dev.mysql.com/doc/mysql/en/MERGE.html On Wed, 20 Oct 2004 11:09:43 +0100, Phil Bitis [EMAIL PROTECTED] wrote: Thanks for the informative reply Sergei, We're actually just using an INT field at the moment, we were going to move over to BIGINT when we start using 64-bit MySQL (soon). Do you know where I should look for information on writing our own table handler? Thanks, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Hi! On Oct 21, Mads Kristensen wrote: *snip* Yes. B-tree is always balanced: http://www.nist.gov/dads/HTML/btree.html Regards, Sergei *snip* You are right, B+Trees are always balanced but When you insert in increasing order all your inserts will be to the last leaf of the B+tree. This means that you can get some concurrency problems when updating the index since it is always the same part of the index that needs to be locked. I'm not quite sure how MySQL does it locking, but if it locks only the index leafs that it is updating this kind of insertion will give poor performance compared to random insertion. MyISAM uses table-level locks, so it always lock the complete index for writes. On the other hand, always writting to the last leaf, you constantly touch only few nodes - on the path from the root to the last leaf. Thus these nodes/keypages will be always cached and the performance should be somewhat better as compared to random accesses (assuming the index is too big to fit in cache completely). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Hi! On Oct 21, Phil Bitis wrote: From: Sergei Golubchik [EMAIL PROTECTED] But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. Hi again, does the key page size differ depending on the type of the column (BIGINT, INT, etc)? Is there any way I can work out the key page size, or configure it? It differs. No, I don't think it can be influenced. It is chosen automatically based on key length - so that longer keys get larger keypages. On the other hand, if you're thinking about new storage engine (and you're in programming, compiling MySQL, etc), you can as well modify MyISAM code to have any keypage size you like. Search in mi_create.c for keydef-block_length=. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Hi! On Oct 23, Phil Bitis wrote: Hello, We want to be able to insert records into a table containing a billion records in a timely fashion. The table has one primary key, which I understand is implemented using B-trees, causing insertion to slow by log N. Corect. But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. The key field is an auto_increment field. The table is never joined to other tables. Is there any way we could implement the index ourselves, by modifying the MyISAM table handler perhaps? Or writing our own? Hmm, MyISAM can only do B-tree indexes. It won't be easy to add a completely different index algorithm to it. Writing your own table handler could be easier. In our setup record n is always the nth record that was inserted in the table, it would be nice to just skip n * recordsize to get to the record. Right, assuming all records have the same length, you can just write nth record at the offest n * recordsize on inserts, and use the value of n as a key on reads. Of course, it's a very specialized storage engine, not that much of general use - but it's very specialized to handle your case, so it can be the fastest solution. Also, could someone shed some light on how B-tree indexes work. Do they behave well when values passed in are sequential (1, 2, 3, ...) rather than random values? Yes. B-tree is always balanced: http://www.nist.gov/dads/HTML/btree.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Thanks for the reply. We're actually using DELAY_KEY_WRITE on our tables. The manual says (7.2.15): Declaring a MyISAM table with the DELAY_KEY_WRITE=1 table option makes index updates faster because they are not flushed to disk until the table is closed. Does this work with all types of indexes, including primary keys? - Original Message - From: mos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 4:20 AM Subject: Re: B-tree index question Phil, The fastest method to load data into a table is to use Load Data Infile. If the table is empty when the command is executed, then the index is not updated until after the command completes. Otherwise if you are loading a lot of data, you may want to drop the index and rebuild it later. Unfortunately Alter Table table_name disable keys won't work on unique indexes (primary). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Thanks for the informative reply Sergei, We're actually just using an INT field at the moment, we were going to move over to BIGINT when we start using 64-bit MySQL (soon). Do you know where I should look for information on writing our own table handler? Thanks, -Phil - Original Message - From: Sergei Golubchik [EMAIL PROTECTED] To: Phil Bitis [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 9:23 AM Subject: Re: B-tree index question Hi! On Oct 23, Phil Bitis wrote: Hello, We want to be able to insert records into a table containing a billion records in a timely fashion. The table has one primary key, which I understand is implemented using B-trees, causing insertion to slow by log N. Corect. But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. The key field is an auto_increment field. The table is never joined to other tables. Is there any way we could implement the index ourselves, by modifying the MyISAM table handler perhaps? Or writing our own? Hmm, MyISAM can only do B-tree indexes. It won't be easy to add a completely different index algorithm to it. Writing your own table handler could be easier. In our setup record n is always the nth record that was inserted in the table, it would be nice to just skip n * recordsize to get to the record. Right, assuming all records have the same length, you can just write nth record at the offest n * recordsize on inserts, and use the value of n as a key on reads. Of course, it's a very specialized storage engine, not that much of general use - but it's very specialized to handle your case, so it can be the fastest solution. Also, could someone shed some light on how B-tree indexes work. Do they behave well when values passed in are sequential (1, 2, 3, ...) rather than random values? Yes. B-tree is always balanced: http://www.nist.gov/dads/HTML/btree.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
If you are using MyISAM tables, have you thought about using MERGE tables instead? You could partition your table into several smaller tables. I don't know how the performance would be on a billion record table, but from my understanding it would shrink your index down. http://dev.mysql.com/doc/mysql/en/MERGE.html On Wed, 20 Oct 2004 11:09:43 +0100, Phil Bitis [EMAIL PROTECTED] wrote: Thanks for the informative reply Sergei, We're actually just using an INT field at the moment, we were going to move over to BIGINT when we start using 64-bit MySQL (soon). Do you know where I should look for information on writing our own table handler? Thanks, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
B-tree index question
Hello, We want to be able to insert records into a table containing a billion records in a timely fashion. The table has one primary key, which I understand is implemented using B-trees, causing insertion to slow by log N. The key field is an auto_increment field. The table is never joined to other tables. Is there any way we could implement the index ourselves, by modifying the MyISAM table handler perhaps? Or writing our own? In our setup record n is always the nth record that was inserted in the table, it would be nice to just skip n * recordsize to get to the record. Also, could someone shed some light on how B-tree indexes work. Do they behave well when values passed in are sequential (1, 2, 3, ...) rather than random values? Thanks in advance, -Phil
Re: B-tree index question
At 04:15 PM 10/23/2004, you wrote: Hello, We want to be able to insert records into a table containing a billion records in a timely fashion. The table has one primary key, which I understand is implemented using B-trees, causing insertion to slow by log N. The key field is an auto_increment field. The table is never joined to other tables. Is there any way we could implement the index ourselves, by modifying the MyISAM table handler perhaps? Or writing our own? In our setup record n is always the nth record that was inserted in the table, it would be nice to just skip n * recordsize to get to the record. Also, could someone shed some light on how B-tree indexes work. Do they behave well when values passed in are sequential (1, 2, 3, ...) rather than random values? Thanks in advance, -Phil Phil, The fastest method to load data into a table is to use Load Data Infile. If the table is empty when the command is executed, then the index is not updated until after the command completes. Otherwise if you are loading a lot of data, you may want to drop the index and rebuild it later. Unfortunately Alter Table table_name disable keys won't work on unique indexes (primary). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index Question
Hello,
I'm trying to create some summary data using some existing InnoDB tables
and I'm running into performance issues. Here is the query:
select topicx, count(*) from BillVote t1 left join BillVotestudentRelation
t3 on t1.mvcoid=t3.idstudent_p left join Student t2 on
t3.idstudent_c=t2.mvcoid left join BillVotebillRelation t5 on
t1.mvcoid=t5.idbill_p left join Bill t4 on t5.idbill_c=t4.mvcoid where
(((t2.usernamex)=(?) and (t4.committeeStatusx)=('P'))) group by topicx
The MySQL EXPLAIN command provided the following information:
+---++--+-+-++---+--+
| table | type | possible_keys| key
| key_len | ref| rows | Extra
|
+---++--+-+-++---+--+
| t1| index | NULL |
PRIMARY | 32 | NULL | 33297 | Using index; Using temporary;
Using filesort |
| t3| ref| PRIMARY |
PRIMARY | 32 | t1.mvcoid | 1 | Using index
|
| t2| eq_ref | PRIMARY,usernamex|
PRIMARY | 32 | t3.idstudent_c | 1 | Using where
|
| t5| ref| PRIMARY |
PRIMARY | 32 | t1.mvcoid | 1 | Using index
|
| t4| eq_ref | PRIMARY,committeeStatusx,committeeStatusx_topicx |
PRIMARY | 32 | t5.idbill_c| 1 | Using where
|
+---++--+-+-++---+--+
Is there an index I can add to improve the performance of this query? Any
help would be greatly appreciated.
Thanks.
Jeff Gunther
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question part 2
rmck [EMAIL PROTECTED] wrote: I understand that I need to update the db's cardinality for this table I need speed Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one because with 56179085 records this could take a while... myisamchk -a does the same as ANALYZE TABLE. You can use either of them. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question
I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
- Original Message - From: rmck [EMAIL PROTECTED] Are my indexes all gone?? If so how do I recover them! Thanks hehe ;-) MySQL just doesn't know the cardinality of the indexes yet (the cardinality number is a *guess*) Try CHECK TABLE and I think you will see the cardinality numbers appear again. Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
- Original Message - From: rmck [EMAIL PROTECTED] Are my indexes all gone?? If so how do I recover them! Thanks hehe ;-) MySQL just doesn't know the cardinality of the indexes yet (the cardinality number is a *guess*) Try CHECK TABLE and I think you will see the cardinality numbers appear again. Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Did you run an ANALYZE TABLE? Original Message On 2/4/04, 9:33:30 AM, rmck [EMAIL PROTECTED] wrote regarding index question: I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+ -+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+ -+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+ -+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+ -+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+ -+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+ -+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question part 2
I understand that I need to update the db's cardinality for this table I need speed Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one because with 56179085 records this could take a while... Thanks for the replies Rob -Forwarded Message- From: rmck [EMAIL PROTECTED] Sent: Feb 4, 2004 7:33 AM To: [EMAIL PROTECTED] Subject: index question I ran an insert..select from one table to the other ( changed some column types to int from varchar on new table). the insert went fine. mysql INSERT INTO Feb04_int SELECT * from Feb04; Query OK, 56179085 rows affected (3 hours 15 min 52.89 sec) Records: 56179085 Duplicates: 0 Warnings: 0 but I notice now when I run show index it looks like it is not correct: before: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A | 125680 | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |11235817 | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |14044771 | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.00 sec) now: mysql SHOW INDEX FROM Feb04; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | Feb04 | 0 | PRIMARY |1 | ID | A |56179085 | NULL | NULL | | BTREE | | | Feb04 | 1 | AllIndex |1 | laddr | A |NULL | 12 | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |2 | rport | A |NULL | NULL | NULL | YES | BTREE | | | Feb04 | 1 | AllIndex |3 | raddr | A |NULL | 12 | NULL | YES | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.02 sec) Are my indexes all gone?? If so how do I recover them! Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index Question
John Berman [EMAIL PROTECTED] wrote: Hi. using MYSql 3.28 There is no such version of MySQL :) I have a surname column with a standard index and this is the column were search are performed on, currently the filed only has the one name i.e.: Surname: smith I want to include other column data in the search i.e. fathersname, so I create an index on that column and in my search I use a statement like: surname = ' globsurname ' or fathersname = ' globsurname ' this does work but tends to slow the thing down, Because MySQL doesn't optimize search on two different keys with OR: http://www.mysql.com/doc/en/Searching_on_two_keys.html So Can I take the name from fathersname and include it in the Surname field so I have say: Surname: smith jones It does not seem to work for me, maybe I need a particular separator ? I want to go this way as each record my have several names that I want to be able to search on. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index Question
Hi. using MYSql 3.28 I have a surname column with a standard index and this is the column were search are performed on, currently the filed only has the one name i.e.: Surname: smith I want to include other column data in the search i.e. fathersname, so I create an index on that column and in my search I use a statement like: surname = ' globsurname ' or fathersname = ' globsurname ' this does work but tends to slow the thing down, So Can I take the name from fathersname and include it in the Surname field so I have say: Surname: smith jones It does not seem to work for me, maybe I need a particular separator ? I want to go this way as each record my have several names that I want to be able to search on. Thanks in advance Regards John Berman
index question
Ok, don't shoot me for not entirely understanding indexes. Can you build an index across two different tables w/in the same database? I need an index on fields in table a and in table b and I want that index to exist in table a. Is it possible? I'm running 4.0.12. TIA, Charlie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Lists - Jump [EMAIL PROTECTED] wrote: Ok, don't shoot me for not entirely understanding indexes. Can you build an index across two different tables w/in the same database? I need an index on fields in table a and in table b and I want that index to exist in table a. Is it possible? I'm running 4.0.12. Nope. You can't. Why do you need such index? Create the separate indexes. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question
Hello Guys, I have the following table: CREATE TABLE tempo_resposta ( id int(11) NOT NULL auto_increment, idmaquina int(11) NOT NULL default '0', tempo int(11) NOT NULL default '0', horario datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; In this table, I'll be running this query: select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=SOMEID order by diferenca desc Right now, seems table is being completly scanned with this query: mysql explain select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=23 order by diferenca desc; ++--+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--+ -+ | tempo_resposta | ALL | NULL | NULL |NULL | NULL | 9216 | Using where; Using filesort | ++--+---+--+-+--+--+ -+ 1 row in set (0.00 sec) rows=9216, exactly all rows in the table Question: is there a way of creating an index for helping that kind of query ? I've tried creating index on horario, but it doesnt helped. Sincerily, Leonardo Rodrigues -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Hi, You need to index the column that is used in the where clause try this ALTER TABLE tempo_resposta ADD INDEX idmaquina (idmaquina); HTH Dobromir Velev - Original Message - From: Leonardo Rodrigues Magalhães [EMAIL PROTECTED] To: MySQL ML [EMAIL PROTECTED] Sent: Friday, June 13, 2003 17:21 Subject: index question Hello Guys, I have the following table: CREATE TABLE tempo_resposta ( id int(11) NOT NULL auto_increment, idmaquina int(11) NOT NULL default '0', tempo int(11) NOT NULL default '0', horario datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; In this table, I'll be running this query: select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=SOMEID order by diferenca desc Right now, seems table is being completly scanned with this query: mysql explain select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=23 order by diferenca desc; ++--+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--+ -+ | tempo_resposta | ALL | NULL | NULL |NULL | NULL | 9216 | Using where; Using filesort | ++--+---+--+-+--+--+ -+ 1 row in set (0.00 sec) rows=9216, exactly all rows in the table Question: is there a way of creating an index for helping that kind of query ? I've tried creating index on horario, but it doesnt helped. Sincerily, Leonardo Rodrigues -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
Something else to consider here: MySQL can use indexes to optimize order by as well, but your order by value can't be indexed since it is derived from a database lookup/calculation. If you really need to order by diferenca you may have to live with it, although you may still see Using filesort(which would indicate a slower query) in your explain since MySQL may have to make a second pass to sort the order by. Check out: http://www.mysql.com/doc/en/ORDER_BY_optimisation.html http://www.mysql.com/doc/en/MySQL_indexes.html Dobromir Velev [EMAIL PROTECTED] 06/13/03 10:03AM Hi, You need to index the column that is used in the where clause try this ALTER TABLE tempo_resposta ADD INDEX idmaquina (idmaquina); HTH Dobromir Velev - Original Message - From: Leonardo Rodrigues Magalhães [EMAIL PROTECTED] To: MySQL ML [EMAIL PROTECTED] Sent: Friday, June 13, 2003 17:21 Subject: index question Hello Guys, I have the following table: CREATE TABLE tempo_resposta ( id int(11) NOT NULL auto_increment, idmaquina int(11) NOT NULL default '0', tempo int(11) NOT NULL default '0', horario datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; In this table, I'll be running this query: select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=SOMEID order by diferenca desc Right now, seems table is being completly scanned with this query: mysql explain select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from tempo_resposta where idmaquina=23 order by diferenca desc; ++--+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--+ -+ | tempo_resposta | ALL | NULL | NULL |NULL | NULL | 9216 | Using where; Using filesort | ++--+---+--+-+--+--+ -+ 1 row in set (0.00 sec) rows=9216, exactly all rows in the table Question: is there a way of creating an index for helping that kind of query ? I've tried creating index on horario, but it doesnt helped. Sincerily, Leonardo Rodrigues -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question
At 1:19 -0600 11/6/02, D. Walton wrote: At 01:05 AM 11/6/2002 -0600, you wrote: Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. From Jeremy's post, no such thing appears. He answered your question correctly. You were just asking the wrong question. :-) Use a single three-column index. Drop the two-column one and create a new one with the third column added. Paul, I'm not sure I understand. Are you agreeing with Jeremy or are you saying there is a solution, but I didn't ask my original question correctly? I'm afraid I don't see how using a single three-column index solves this? If I use a single three-column unique index then I can potentially add two rows with the same 'id'/'date' pairs so long as they both have a different 'value'. I need for no two records to have the same 'id'/'date' yet I also need the 'value' in the index for maximum lookup speed. Okay, I didn't understand that last part properly. Sorry. If you want to enforce uniqueness at the id/date level, you will need a separate unique index on just those two columns, in addition to a three column index on id/date/value. There is no syntax for specifying that you want an index on a set of columns but to enforce a uniqueness constraint on just a subset of those columns. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
index question
I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? If it creates a totally separate index then it will just have to update two indexes for every insert which in the end will slow things down. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) If it creates a totally separate index then it will just have to update two indexes for every insert which in the end will slow things down. Yes. It's a design tradeoff you need to consider. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 91 days, processed 1,905,923,218 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) Seriously, it seems like there should be an optimizer in there that could pick out the fact that the second index is simply a subset of the primary key. It's very simple logic, even if this situation very seldomly occurs. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 20:39 -0600 11/5/02, D. Walton wrote: At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) In that case, what's necessary is for you to realize that you need do nothing. :-) If you have an index on id, date, and value, then id, date is a leftmost prefix of that index, and MySQL will happily use it. You need not create an explicit index on id, date. Nor on just id. Seriously, it seems like there should be an optimizer in there that could pick out the fact that the second index is simply a subset of the primary key. It's very simple logic, even if this situation very seldomly occurs. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 10:32 PM 11/5/2002 -0600, you wrote: At 20:39 -0600 11/5/02, D. Walton wrote: At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) In that case, what's necessary is for you to realize that you need do nothing. :-) If you have an index on id, date, and value, then id, date is a leftmost prefix of that index, and MySQL will happily use it. You need not create an explicit index on id, date. Nor on just id. Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 23:31 -0600 11/5/02, D. Walton wrote: At 10:32 PM 11/5/2002 -0600, you wrote: At 20:39 -0600 11/5/02, D. Walton wrote: At 05:18 PM 11/5/2002 -0800, you wrote: On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote: I have a table with 3 fields, 'id', 'date', and 'value'. I've created a unique index on 'id' and 'date' in order to lookup 'value' quickly. I would like to be able to add 'value' to the index so that the data files does not have to be referenced and will allow faster lookups and groupings by date, however, I can't lose the ability to do insert ignore on the 'id' and 'date' unique index. So the question is, if I create a primary key of 'id', 'date', 'value', and then create a secondary unique index of 'id' and 'date' will MySQL simply reuse the primary key for the secondary unique index or will it create a totally separate index on the disk? It will create a totally separate index, since that's what you told it to do. :-) Well, in that case, how do I tell it to do what I want it to do? ;-) In that case, what's necessary is for you to realize that you need do nothing. :-) If you have an index on id, date, and value, then id, date is a leftmost prefix of that index, and MySQL will happily use it. You need not create an explicit index on id, date. Nor on just id. Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. From Jeremy's post, no such thing appears. He answered your question correctly. You were just asking the wrong question. :-) Use a single three-column index. Drop the two-column one and create a new one with the third column added. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: index question
At 01:05 AM 11/6/2002 -0600, you wrote: Paul, the point was to have 'value' be part of the primary key for pure lookup speed (data file would not need be referenced), but not to have it effect the uniqueness of the 'id'/'date' key pair so that I could do an 'insert ignore' into the table with a 'value' of 0 and if a record with matching 'id'/'date' already existed with a 'value' of 4 then it would not create another record. I could do this with two indexes but using two indexes would more than negate the benefit of not having to lookup in the data file. Yet it's perfectly reasonable to have mysql use the same index for both of these index definitions, but from Jeremy's post it appears that mysql won't do this optimization. From Jeremy's post, no such thing appears. He answered your question correctly. You were just asking the wrong question. :-) Use a single three-column index. Drop the two-column one and create a new one with the third column added. Paul, I'm not sure I understand. Are you agreeing with Jeremy or are you saying there is a solution, but I didn't ask my original question correctly? I'm afraid I don't see how using a single three-column index solves this? If I use a single three-column unique index then I can potentially add two rows with the same 'id'/'date' pairs so long as they both have a different 'value'. I need for no two records to have the same 'id'/'date' yet I also need the 'value' in the index for maximum lookup speed. -Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie Index Question
I have just begun playing around with MySQL and I have a question about indexing on dates. Part of our system is a work flow management system and we have a table for all documents that we receive. Each document record has several dates to indicate when various processing functions have been completed. In addition to the date we also wish to know the time. In setting up my first table I set the dates up as DATETIME fields. However, indexing on these forces me to specify a date and time or a range. i.e. where ReceivedDate = '2002-07-26 08:15:05' or where ReceivedDate = '2002-07-26' and ReceivedDate '2002-07-27' Since most of the queries I would run that would take the date into consideration would be to find every document that was received on a given date am I better off making two fields, one for ReceivedDate and one for ReceivedTime or is there a way to create an index only on the Date portion of a DATETIME field? Thanks for the help. Darrell - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie Index Question
Hi. On Fri 2002-07-26 at 08:34:51 -0400, [EMAIL PROTECTED] wrote: [...] set the dates up as DATETIME fields. However, indexing on these forces me to specify a date and time or a range. i.e. where ReceivedDate = '2002-07-26 08:15:05' or where ReceivedDate = '2002-07-26' and ReceivedDate '2002-07-27' Since most of the queries I would run that would take the date into consideration would be to find every document that was received on a given date am I better off making two fields, one for ReceivedDate and one for ReceivedTime or is there a way to create an index only on the Date portion of a DATETIME field? Your perception of the concept index seems a bit bend. Even if you could specify such an index, you would still have the same problem, as in queries you can access columns, not indexes. Indexes are only used by the query optimizer to retrieve the rows in question faster. Ideally, this is a transpararent mechanism. In your example, the index on ReceivedDate will also be used to retrieve the result for WHERE ReceivedDate = '2002-07-26' AND ReceivedDate '2002-07-27' To accomplish what you want, you could write the Date with LIKE, e.g. WHERE ReceivedDate LIKE '2002-07-26 %' but this would quite surely prevent the use of any index on this column, as would any try to extract the date part with a function (this would be an expression and expressions cannot use indexes in MySQL). You could, as you suggested, split this into two fields. And if your common usage does not use the time part, this looks like a reasonable solution. I, personally, need the time part more often and simply write my queries this way: WHERE Received BETWEEN '$date 00:00:00' AND '$date 23:59:59' where $date is a variable from my application. Not really pretty, but works fine. In short: There probably is not The One, Right Solution. Greetings, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql index question
Regarding mysql... 1) Are primary keys and foreign keys by default indexes for a table? 2) Do I have to use a special data other than float to allow for negative numbers, i.e. I want to be able to enter -1.76 and be able to later do math against that number... Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql index question
From my understanding primary keys and foreign keys are indexed. Someone correct me if I'mwrong here. And you shouldn't have to do anything else to field besides make it a float to usenegative numbers. -Nick Regarding mysql... 1) Are primary keys and foreign keys by default indexes for a table? 2) Do I have to use a special data other than float to allow for negative numbers, i.e. I want to be able to enter -1.76 and be able to later do math against that number... Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail mysql- [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql index question
Taylor, Thursday, May 16, 2002, 4:22:37 PM, you wrote: TL Regarding mysql... TL 1) Are primary keys and foreign keys by default indexes for a table? Yeah. Primary key is index by default. Foreign key constraints you can create only on indexed column. TL 2) Do I have to use a special data other than float to allow for negative numbers, i.e. I want to be able to enter -1.76 and be able to later do math against that number... What about decimal? Look at: http://www.mysql.com/doc/C/o/Column_types.html TL Thanks, TL Taylor -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FULLTEXT index question
Environment:
MySQL Version 3.23.42
FreeBSD 4.4
ISAM table of about 300,000 rows and 10 columns
with a column structured as varchar(50) upon which
exists a FULLTEXT index
Behavior:
The query
select * from tablename where match(column_name)
against('tree');
selects seven records.
The query
select * from tablename where match(column_name)
against('three');
fails to match any record.
Ten to twelve records in the table are know to have
the distinct word three in them in the indexed column.
Question:
Is this in any way expected behavior?
Thanks for any enlightenment.
query sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FULLTEXT index question
Hi!
On Apr 15, Nicholas Murphy wrote:
The query
select * from tablename where match(column_name)
against('three');
fails to match any record.
Ten to twelve records in the table are know to have
the distinct word three in them in the indexed column.
Question:
Is this in any way expected behavior?
Yes.
-
% grep three myisam/ft_static.c
three,
%
-
That is three is present in the stopword list.
Of course, you can remove it from there and rebuild MySQL
(and your indexes).
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [Newbie] MATCH and INDEX question (using a JOIN in a MATCH statement)
Well after some pondering I found a pretty easy work around:
SELECT main.id, description, maincat.name, subcat.name from main
LEFT JOIN maincat ON maincat.id=main.maincatid
LEFT JOIN subcat ON subcat.id=main.subcatid
WHERE MATCH (description) AGAINST ('keywords')
OR
WHERE MATCH (maincat.name) AGAINST ('keywords')
OR
WHERE MATCH (subcat.name) AGAINST ('keywords')
You can even add the MATCHes to your select to get a total relavence:
(MATCH (description) AGAINST ('keywords') + MATCH (maincat.name) AGAINST
('keywords') + MATCH (subcat.name) AGAINST ('keywords')) as relavence
which you can also sort by.
I hope this helps somebody.
Dan
At 13:43 -0700 3/26/02, Dan Tappin wrote:
This is a follow-up to a MySQL keyword text search question I had answer a
few days ago.
I have table 'main' which has two INT columns 'maincat' and 'subcat' which
hold an index number from to other tables maincat and subcat. These table
each hold descriptive names ('name') for each of the main categories and sub
categories.
I have no problem using MATCH to search the various fields of 'main' using
an INDEX. The problem is I want to create a search across the related
'name' fields of the other two tables.
Here is what I have so far:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description) AGAINST
('keywords')
This works but I can only MATCH in the columns of 'main'.
Now my first thought was to try this:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description,
maincat.name, subcat.name) AGAINST ('keywords')
Which results in an error.
Can anyone tell me if I can do this using MATCH?
Nope.
The columns named in the MATCH() must match the columns for a FULLTEXT
index, and indexes don't cross tables.
Thanks,
Dan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [Newbie] MATCH and INDEX question (using a JOIN in a MATCH statement)
Thanks Paul,
I figured that was the answer. Do I need to perform a second and third
query from my related tables with a join back to the 'main' table?
Example:
select main.id, main.description, maincat.name, subcat.name from main LEFT
JOIN maincat ON main.maincatid=maincat.id LEFT JOIN subcat ON
main.subcatid=subcat.id WHERE MATCH (name) AGAINST ('keywords') WHERE MATCH
(description, etc, etc2, etc3 ) AGAINST ('keywords')
select main.id, main.description, maincat.name, subcat.name from maincat
LEFT JOIN main ON maincat.id=main.maincat LEFT JOIN subcat ON subcat.id
=main.subcatid WHERE MATCH (name) AGAINST ('keywords')
select main.id, main.description, maincat.name, subcat.name from subcat LEFT
JOIN main ON subcat.id=main.subcat LEFT JOIN maincat ON maincat.id
=main.maincatid WHERE MATCH (name) AGAINST ('keywords')
I would then have 3 arrays that I could join and sort and use as my results.
I am not sure if this is more work than it is worth. I don't want to
duplicate data but I am thinking of just adding a 'maincatname' and
'subcatname' columns to my 'main' table and update them from the related
tables.
Thanks for your help,
Dan
At 13:43 -0700 3/26/02, Dan Tappin wrote:
This is a follow-up to a MySQL keyword text search question I had answer a
few days ago.
I have table 'main' which has two INT columns 'maincat' and 'subcat' which
hold an index number from to other tables maincat and subcat. These table
each hold descriptive names ('name') for each of the main categories and sub
categories.
I have no problem using MATCH to search the various fields of 'main' using
an INDEX. The problem is I want to create a search across the related
'name' fields of the other two tables.
Here is what I have so far:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description) AGAINST
('keywords')
This works but I can only MATCH in the columns of 'main'.
Now my first thought was to try this:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description,
maincat.name, subcat.name) AGAINST ('keywords')
Which results in an error.
Can anyone tell me if I can do this using MATCH?
Nope.
The columns named in the MATCH() must match the columns for a FULLTEXT
index, and indexes don't cross tables.
Thanks,
Dan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[Newbie] MATCH and INDEX question (using a JOIN in a MATCHstatement)
This is a follow-up to a MySQL keyword text search question I had answer a
few days ago.
I have table 'main' which has two INT columns 'maincat' and 'subcat' which
hold an index number from to other tables maincat and subcat. These table
each hold descriptive names ('name') for each of the main categories and sub
categories.
I have no problem using MATCH to search the various fields of 'main' using
an INDEX. The problem is I want to create a search across the related
'name' fields of the other two tables.
Here is what I have so far:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description) AGAINST
('keywords')
This works but I can only MATCH in the columns of 'main'.
Now my first thought was to try this:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description,
maincat.name, subcat.name) AGAINST ('keywords')
Which results in an error.
Can anyone tell me if I can do this using MATCH?
Thanks,
Dan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [Newbie] MATCH and INDEX question (using a JOIN in a MATCH statement)
At 13:43 -0700 3/26/02, Dan Tappin wrote:
This is a follow-up to a MySQL keyword text search question I had answer a
few days ago.
I have table 'main' which has two INT columns 'maincat' and 'subcat' which
hold an index number from to other tables maincat and subcat. These table
each hold descriptive names ('name') for each of the main categories and sub
categories.
I have no problem using MATCH to search the various fields of 'main' using
an INDEX. The problem is I want to create a search across the related
'name' fields of the other two tables.
Here is what I have so far:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description) AGAINST
('keywords')
This works but I can only MATCH in the columns of 'main'.
Now my first thought was to try this:
select main.id, description, maincat.name, subcat.name from main LEFT JOIN
clients ON forsale.clientid=clients.id WHERE MATCH (description,
maincat.name, subcat.name) AGAINST ('keywords')
Which results in an error.
Can anyone tell me if I can do this using MATCH?
Nope.
The columns named in the MATCH() must match the columns for a FULLTEXT
index, and indexes don't cross tables.
Thanks,
Dan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
index question strange behavior
Questions about index mysql show index from listing; +-++-+--+-+- --+-+--+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +-++-+--+-+- --+-+--+ | listing | 0 | PRIMARY |1 | my_key | A | 137677 | NULL | | listing | 1 | mls_index |1 | mls_number | A | 137677 | NULL | | listing | 1 | sold_new|1 | sold_new| A | 2 | NULL | | listing | 1 | index3 |1 | city| A | 57 | NULL | | listing | 1 | stat_city_price |1 | stat| A | 45892 | NULL | | listing | 1 | stat_city_price |2 | city| A | 45892 | NULL | | listing | 1 | stat_city_price |3 | price | A | 45892 | NULL | +-++-+--+-+- --+-+--+ 7 rows in set (0.00 sec) stat and city are enum types price is an int. mysql explain select mls_number from listing where stat='A' and city='AH'; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 304 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) Question - Why is key len,ref null ?? Is it not using my key? mysql explain select mls_number from listing where stat='S' and city='AH'; +-+--++-+-+- -+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--++-+-+- -+--+---+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +-+--++-+-+- -+--+---+ 1 row in set (0.00 sec) This is what I expect mysql explain select mls_number from listing where stat='S' and city='AH' and price0 and price50 order by price; +-+--++-+-+- -+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--++-+-+- -+--+---+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +-+--++-+-+- -+--+---+ 1 row in set (0.00 sec) why didn't it use price in the key?? mysql explain select mls_number from listing where (stat='B') and city='AH' and price0 and price50 order by price; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 29 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) mysql explain select mls_number from listing where (stat='S' or stat='B') and city='AH' and price0 and price50 order by price; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 2535 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) Why didn't mysql use the key and 689 +29 != 2535 for rows?? stat and city are enum types price is an int Any pointers would be helpful trying to speed up queries they currently take about 16 sec table has 134000 rows. Thanks, Rick [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
Index Question
Hello All, I've got a question that is likely an easy one, I just want confirmation from my peers. I have tables with a timestamp column and perform many selects and counts from these tables based on the date that the record was written. Can/should I index a timestamp column? I do my best to index columns that I specify in my WHERE clauses, however I haven't yet tried to index a timestamp column. Is it even possible? Is it advisable? Should I treat it as a char and limit it to just index on the portion of the data that distinguishes the date (I don't care about the time, just the date). Thanks! Tyrone Mills = Internet service provided by telus.net http://www.telus.net/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index Question
From: [EMAIL PROTECTED] Can/should I index a timestamp column? Can: Yes. Should: Why not? Should I treat it as a char and limit it to just index on the portion of the data that distinguishes the date Hmm, I wouldn't. Offhand, I'd guess that MySQL handles time/date things as integers. I'm pretty sure that he also handles indexes as some sort of integer based serialization. Meaning that conversion to a string would be an extra step and thereby a loss. I'd say to try it a few different ways (if you have the time on this job) and find out what works best for you. --- Rodney Broom Programmer: Desert.Net Spam filter: sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Another index question
For the following select: SELECT DISTINCT badge_history.xact_date AS xact_date, badge_history.xact_time AS xact_time, badge_history.last_name AS last_name, badge_history.bid AS bid, badgests.cond_desc AS status, department.description AS department, badge_history.reader_desc AS reader_desc, area.description AS area, badge.expired_date AS expired, badge_history.xact_type AS xact_type, badge_history.tzcorr AS tzcorr FROM badge_history, badgests, department, area, badge LEFT JOIN smccm_user_vs_permitted_department ON badge_history.dept = smccm_user_vs_permitted_department.permitted_department WHERE smccm_user_vs_permitted_department.smccm_user = '1' AND badge_history.dept = department.id AND badge_history.area = area.id AND badge_history.status = badgests.id AND badge_history.source_host = area.source_host AND badge_history.bid = badge.bid The badge table has 85,000 records, and the badge_history has 4,000,000. This search takes a very long time to run. What indexes should I have on these 2 tables to make it run in a reasonable amount of time (5 minutes), or should I just get Oracle or something? I have indexed like this: alter table badge_history add index ixBigIx (dept, area, status, source_host, bid) This does not help much, it just causes all of the badge table to be pulled in. There is already an index on the bid col in the badge table. I really need this to get working, I've been adding indexes all day and can't get the query to list less than 25 million rows when I describe it. Any advice is appreciated. Mike. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Another index question
Did you try EXPLAIN before SELECT? http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#EXPLAIN Mike Baranski a écrit : For the following select: SELECT DISTINCT badge_history.xact_date AS xact_date, (...) -- Marc Delisle Service de l'informatique Collège de Sherbrooke, Québec - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database Index Question
Does it help to put an Index on a column when you use a LIKE query. ex. SELECT * FROM users WHERE name LIKE '%tom%' Thanks in Advance! __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Database Index Question
At 9:31 AM -0700 4/26/01, Shane Gentry wrote: Does it help to put an Index on a column when you use a LIKE query. ex. SELECT * FROM users WHERE name LIKE '%tom%' Thanks in Advance! In this case, It won't help, since you have a wildcard at the beginning of your pattern. If you used ...LIKE 'tom%' instead, I believe MySQL WOULD use an index on 'name.' -steve -- +-- KDVS 90.3fm Annual Fundraiser : 16 - 22 April 2001 --+ | Steve Edberg University of California, Davis | | [EMAIL PROTECTED] Computer Consultant | | http://aesric.ucdavis.edu/ http://pgfsun.ucdavis.edu/ | +- www.kdvs.org -+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Index Question
My question is about indexes...basically, I'm wondering how many indexes is too much, and what the drawbacks are of having more indexes on a table? I'm guessing INSERT and UPDATE queries probably take longer? My table has the following fields: member_id, first_name, last_name, username, password, email, street1, street2, city, state, zip, country_id, signup_date, signup_ip, ref_id, sex, age, income I know for sure I will want to index the username and email fields. However, should I index the entire field, or would 5 or 6 characters be enough to index? I will also be selecting BY country_id, signup_date, and ref_id as well, is it okay to index all of them? I will also be using the signup_ip field to delete duplicate accounts, should I therefore index that field so the "distinct" operator works better? Is it okay to have 6 indexes on a table like this? The table may have upwards of a million entries. TIA! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Index Question(again).
No Body answered my previous mail. plz help me. I have three tables. i) student_info3 i) grade_ex3 i) test_info and, There are those Index In student_info3 table : index(student_no) In grade_ex3 table : index(student_no, test_no) In test_info table : index(test_no) When I use this SELECT statment, MySQL use indexes well. mysql explain select a.student_no from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+--+-+ + | table | type | possible_keys | key | key_len | ref | rows | Extra || +---++---+--+-+---+--+-+ + | a | index | student_no| student_no | 4 | NULL | 10 | Using |index | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where |used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no |1 | Using |index | +---++---+--+-+---+--+-+ + 3 rows in set (0.00 sec) But When I use this kind of SELECT statement, Table a does not use index anyway. mysql explain select a.* from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+--+-+ + | table | type | possible_keys | key | key_len | ref | rows | Extra || +---++---+--+-+---+--+-+ + | a | ALL| student_no| NULL |NULL | NULL | 10 | || | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where |used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no |1 | Using |index | +---++---+--+-+---+--+-+ + 3 rows in set (0.00 sec) Somebody help me! here are other information. mysql explain select b.korean from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---++-+---+--+-+ + | table | type | possible_keys | key| key_len | ref | rows | Extra | | +---++---++-+---+--+-+ + | a | index | student_no| student_no | 4 | NULL | 10 | Using |index | | b | ALL| student_no_2 | NULL |NULL | NULL | 12 | where |used | | c | eq_ref | PRIMARY | PRIMARY| 4 | b.test_no |1 | Using |index | +---++---++-+---+--+-+ + this uses index well. mysql explain select c.test_name from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+--+-+ + | table | type | possible_keys | key | key_len | ref | rows | Extra || +---++---+--+-+---+--+-+ + | a | index | student_no| student_no | 4 | NULL | 10 | Using |index | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where |used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no |1 | || +---++---+--+-+---+--+-+ + 3 rows in set (0.00 sec) --- Member of N.N.R(New Network Research) Visit NNR.OR.KR --- ¿¥ÆĽº°¡ ¸¸µç ÆÈÆÈÇÑ ¸ÞÀÏ, ¿¥ÆÈ (http://www.empal.com) ¹®ÀåÀ¸·Î ã´Â °Ë»ö¿£Áø, ¿¥ÆĽº (http://www.empas.com) ½Å³ª´Â »ýÈ°¹®ÈÁ¤º¸, ½ÃƼ½ºÄÉÀÌÇÁ (http://www.cityscape.co.kr)
RE: Index Question(again).
Hi, For such small tables, does it matter. MySQL will optimise queries the best way it thinks it can. In this case, in the first query, only the index file for student_info3 will be used, so that might have a bearing. In the second query, where all columns from the student table are requested with no restriction on the student_no column, a full table scan is seen as the best way of getting the data. Check the 'How MySQL uses Indexes' section of the manual. Hope this helps Quentin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 20 February 2001 07:30 To: MySQL Maillin List Subject: Index Question(again). No Body answered my previous mail. plz help me. I have three tables. i) student_info3 i) grade_ex3 i) test_info and, There are those Index In student_info3 table : index(student_no) In grade_ex3 table : index(student_no, test_no) In test_info table : index(test_no) When I use this SELECT statment, MySQL use indexes well. mysql explain select a.student_no from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+- -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+---+- -+-+ | a | index | student_no| student_no | 4 | NULL | 10 | Using index | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no | 1 | Using index | +---++---+--+-+---+- -+-+ 3 rows in set (0.00 sec) But When I use this kind of SELECT statement, Table a does not use index anyway. mysql explain select a.* from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+- -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+---+- -+-+ | a | ALL| student_no| NULL |NULL | NULL | 10 | | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no | 1 | Using index | +---++---+--+-+---+- -+-+ 3 rows in set (0.00 sec) Somebody help me! here are other information. mysql explain select b.korean from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---++-+---+-- +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++---++-+---+-- +-+ | a | index | student_no| student_no | 4 | NULL | 10 | Using index | | b | ALL| student_no_2 | NULL |NULL | NULL | 12 | where used | | c | eq_ref | PRIMARY | PRIMARY| 4 | b.test_no |1 | Using index | +---++---++-+---+-- +-+ this uses index well. mysql explain select c.test_name from student_info3 a , grade_ex3 b , test_info c - where a.student_no = b.student_no and b.test_no = c.test_no ; +---++---+--+-+---+- -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+---+- -+-+ | a | index | student_no| student_no | 4 | NULL | 10 | Using index | | b | index | student_no_2 | student_no_2 | 8 | NULL | 12 | where used; Using index | | c | eq_ref | PRIMARY | PRIMARY | 4 | b.test_no | 1 | | +---++---+--+-+---+- -+-+ 3 rows in set (0.00 sec) --- Member of N.N.R(New Network Research) Visit NNR.OR.KR --- ¿¥ÆĽº°¡ ¸¸µç ÆÈÆÈÇÑ ¸ÞÀÏ, ¿¥ÆÈ (http://www.empal.com) ¹®ÀåÀ¸·Î ã´Â °Ë»ö¿£Áø, ¿¥ÆĽº (http://www.empas.com) ½Å³ª´Â »ýÈ°¹®ÈÁ¤º¸, ½ÃƼ½ºÄÉÀÌÇÁ (http://www.cityscape.co.kr) The information
Index question
Hi~ All. I have a question about using index. I have two following tables. mysql explain ex1 ; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | char(10) | YES | MUL | NULL| | | b | int(11) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) mysql explain ex2 ; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | char(15) | YES | MUL | NULL| | | c | int(11) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) When I use following queries, strange thing happen. mysql explain select ex1.a from ex1, ex2 where ex1.a = ex2.a ; +---+---+---+--+-+--+--+-+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | | +---+---+---+--+-+--+--+-+ + | ex1 | index | a | a| 10 | NULL | 10 | Using index | | | ex2 | index | a | a| 15 | NULL | 10 | where used; Using |index | +---+---+---+--+-+--+--+-+ + 2 rows in set (0.00 sec) mysql explain select * from ex1, ex2 where ex1.a = ex2.a ; +---+--+---+--+-+--+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--++ | ex1 | ALL | a | NULL |NULL | NULL | 10 || | ex2 | ALL | a | NULL |NULL | NULL | 10 | where used | +---+--+---+--+-+--+--++ 2 rows in set (0.00 sec) As you can see, First query uses index, but second query does not. And third query is mysql explain select ex2.c from ex1, ex2 where ex1.a = ex2.a ; +---+---+---+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+--+-+ | ex1 | index | a | a| 10 | NULL | 10 | Using index | | ex2 | ALL | a | NULL |NULL | NULL | 10 | where used | +---+---+---+--+-+--+--+-+ 2 rows in set (0.00 sec) Do I make selecting part's columns to INDEX? What's differ? Thank you for advanced answer! --- Member of N.N.R(New Network Research) Visit NNR.OR.KR --- ¿¥ÆĽº°¡ ¸¸µç ÆÈÆÈÇÑ ¸ÞÀÏ, ¿¥ÆÈ (http://www.empal.com) ¹®ÀåÀ¸·Î ã´Â °Ë»ö¿£Áø, ¿¥ÆĽº (http://www.empas.com) ½Å³ª´Â »ýÈ°¹®ÈÁ¤º¸, ½ÃƼ½ºÄÉÀÌÇÁ (http://www.cityscape.co.kr)
Index question
Say I have a table called Discharge that is indexed on Vessel + Voyage + Port (Primary Key). It has thousands of records in it and continuously grows. I now want to delete records where Vessel = "USS ENTERPRISE" Voyage = "005" I want to delete all records with the above Vessel/Voyage. There can be anywhere from 1 to 20 records with that match (different Port). My SQL statement is: DELETE FROM Discharge where Vessel = "USS ENTERPRISE" AND Voyage = "005"; My question is one pertaining to performance. Will MySQL scan through the entire table or will it utilize the index and quickly delete all record with the above statement? If the former, what would be an efficient and quick way to delete records of a partial key? Thanks, Don - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
