RE: How long is my piece of string?
-Original Message- From: Mike Wexler [mailto:[EMAIL PROTECTED]] The best answer is, try it and see. If your tables are small. Adding and removing indices is very quick. And testing queries is also very quick. Enabling the slow query log, and enabling log-long-format can give some hints by logging all queries that don't use indexes. Then you can feed those queries to the EXPLAIN command and figure out why. I dramatically sped up several queries on my database this way. - 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: How long is my piece of string?
. But that is my question! *When* do they provide a performance difference? Obviously my (fairly simple) question (...and beyond what number of records might indices provide faster extraction/presentation?... ) has not been understood. Can anyone else help, please? At 11:22 22/12/02 +1100, Alan McDonald wrote: You need indexes as soon as (or rather just before) they provide a performance difference. Alan -Original Message- From: Iain Lang [mailto:[EMAIL PROTECTED]] Sent: Sunday, 22 December 2002 11:15 AM To: [EMAIL PROTECTED] Subject: How long is my piece of string? . Dear List, I'm using php MySQL for a cycling club website, results, guest-book, events and so on. I've just started and have faithfully created indices all over the place. At present, we have less than 400 records, be they of members, of image URLs, whatever. Each year will, I expect, create an additional 400 records. Am I gilding the lily adding indices for such a small database? Does such a small database really *need* indices, and beyond what number of records might indices provide faster extraction/presentation? I realise how vague a question it is, hence the subject title. Yooors, Iain. - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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 - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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: How long is my piece of string?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Iain Lang wrote: . But that is my question! *When* do they provide a performance difference? Obviously my (fairly simple) question (...and beyond what number of records might indices provide faster extraction/presentation?... ) has not been understood. Can anyone else help, please? At 11:22 22/12/02 +1100, Alan McDonald wrote: You need indexes as soon as (or rather just before) they provide a performance difference. From http://www.mysql.com/doc/en/Where_optimisations.html : Each table index is queried, and the best index that spans fewer than 30% of the rows is used. If no such index can be found, a quick table scan is used.. So if you can't create an index that will have values that will cause MySQL to look at less that 30% of the rows, it will not be used. For example if you have a column that contains the value 'foo' 90% of the time, queries looking for 'foo' in that column will not use an index. Indexes almost _always_ slow down data _modification_. You can tell when/if they speed-up data retrieval by using the 'EXPLAIN' command in MySQL, see: http://www.mysql.com/doc/en/EXPLAIN.html -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+BcGPtvXNTca6JD8RAjAmAKC/OlCFFx7iLZN57AMIRO5cV4wfyACgrLjU OHViqL0GR0Hf4lLoS50uTXU= =veUT -END PGP SIGNATURE- - 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: How long is my piece of string?
Create 2 tables with identical columns definition, one with index and the other without. Start adding records to the two tables and querying them. This way you'll find out the number of recors for which the performance is about the same. After that, you can decide wich way to go. Adolfo -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 9:44 AM To: Iain Lang Cc: Alan McDonald; [EMAIL PROTECTED] Subject: Re: How long is my piece of string? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Iain Lang wrote: . But that is my question! *When* do they provide a performance difference? Obviously my (fairly simple) question (...and beyond what number of records might indices provide faster extraction/presentation?... ) has not been understood. Can anyone else help, please? At 11:22 22/12/02 +1100, Alan McDonald wrote: You need indexes as soon as (or rather just before) they provide a performance difference. From http://www.mysql.com/doc/en/Where_optimisations.html : Each table index is queried, and the best index that spans fewer than 30% of the rows is used. If no such index can be found, a quick table scan is used.. So if you can't create an index that will have values that will cause MySQL to look at less that 30% of the rows, it will not be used. For example if you have a column that contains the value 'foo' 90% of the time, queries looking for 'foo' in that column will not use an index. Indexes almost _always_ slow down data _modification_. You can tell when/if they speed-up data retrieval by using the 'EXPLAIN' command in MySQL, see: http://www.mysql.com/doc/en/EXPLAIN.html -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+BcGPtvXNTca6JD8RAjAmAKC/OlCFFx7iLZN57AMIRO5cV4wfyACgrLjU OHViqL0GR0Hf4lLoS50uTXU= =veUT -END PGP SIGNATURE- - 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 - 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: How long is my piece of string?
Iain Lang wrote: . But that is my question! *When* do they provide a performance difference? Obviously my (fairly simple) question (...and beyond what number of records might indices provide faster extraction/presentation?... ) has not been understood. Can anyone else help, please? Its not that it hasn't been understood. Its not a simple question. It depends on a lot of things you didn't bring up. What is the ration of reads to writes? What is the key of values? What do the queries look like? What hardware are you running on? The best answer is, try it and see. If your tables are small. Adding and removing indices is very quick. And testing queries is also very quick. At 11:22 22/12/02 +1100, Alan McDonald wrote: You need indexes as soon as (or rather just before) they provide a performance difference. Alan -Original Message- From: Iain Lang [mailto:[EMAIL PROTECTED]] Sent: Sunday, 22 December 2002 11:15 AM To: [EMAIL PROTECTED] Subject: How long is my piece of string? . Dear List, I'm using php MySQL for a cycling club website, results, guest-book, events and so on. I've just started and have faithfully created indices all over the place. At present, we have less than 400 records, be they of members, of image URLs, whatever. Each year will, I expect, create an additional 400 records. Am I gilding the lily adding indices for such a small database? Does such a small database really *need* indices, and beyond what number of records might indices provide faster extraction/presentation? I realise how vague a question it is, hence the subject title. Yooors, Iain. - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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 - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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 - 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: How long is my piece of string?
. With about 400 records total - as I said in my original post - , I should imagine that any elapsed time will be more a function of my connection speed than a true measure of database/query speed. At 11:56 22/12/02 -0400, Adolfo Bello wrote: Create 2 tables with identical columns definition, one with index and the other without. Start adding records to the two tables and querying them. This way you'll find out the number of recors for which the performance is about the same. After that, you can decide wich way to go. Adolfo -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 22, 2002 9:44 AM To: Iain Lang Cc: Alan McDonald; [EMAIL PROTECTED] Subject: Re: How long is my piece of string? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Iain Lang wrote: . But that is my question! *When* do they provide a performance difference? Obviously my (fairly simple) question (...and beyond what number of records might indices provide faster extraction/presentation?... ) has not been understood. Can anyone else help, please? At 11:22 22/12/02 +1100, Alan McDonald wrote: You need indexes as soon as (or rather just before) they provide a performance difference. From http://www.mysql.com/doc/en/Where_optimisations.html : Each table index is queried, and the best index that spans fewer than 30% of the rows is used. If no such index can be found, a quick table scan is used.. So if you can't create an index that will have values that will cause MySQL to look at less that 30% of the rows, it will not be used. For example if you have a column that contains the value 'foo' 90% of the time, queries looking for 'foo' in that column will not use an index. Indexes almost _always_ slow down data _modification_. You can tell when/if they speed-up data retrieval by using the 'EXPLAIN' command in MySQL, see: http://www.mysql.com/doc/en/EXPLAIN.html -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+BcGPtvXNTca6JD8RAjAmAKC/OlCFFx7iLZN57AMIRO5cV4wfyACgrLjU OHViqL0GR0Hf4lLoS50uTXU= =veUT -END PGP SIGNATURE- - 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 - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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
How long is my piece of string?
. Dear List, I'm using php MySQL for a cycling club website, results, guest-book, events and so on. I've just started and have faithfully created indices all over the place. At present, we have less than 400 records, be they of members, of image URLs, whatever. Each year will, I expect, create an additional 400 records. Am I gilding the lily adding indices for such a small database? Does such a small database really *need* indices, and beyond what number of records might indices provide faster extraction/presentation? I realise how vague a question it is, hence the subject title. Yooors, Iain. - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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: How long is my piece of string?
You need indexes as soon as (or rather just before) they provide a performance difference. Alan -Original Message- From: Iain Lang [mailto:[EMAIL PROTECTED]] Sent: Sunday, 22 December 2002 11:15 AM To: [EMAIL PROTECTED] Subject: How long is my piece of string? . Dear List, I'm using php MySQL for a cycling club website, results, guest-book, events and so on. I've just started and have faithfully created indices all over the place. At present, we have less than 400 records, be they of members, of image URLs, whatever. Each year will, I expect, create an additional 400 records. Am I gilding the lily adding indices for such a small database? Does such a small database really *need* indices, and beyond what number of records might indices provide faster extraction/presentation? I realise how vague a question it is, hence the subject title. Yooors, Iain. - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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 - 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: How long is my piece of string?
Hi! I was interested in the answer to this person's question, too! The following response: You need indexes as soon as (or rather just before) they provide a performance difference. Alan seems to lack detail in answering the question, which might be phrased when might indices start making a performance difference?. Can anyone give rules of thumb? Cheers! -warren - Original Message - From: Alan McDonald [EMAIL PROTECTED] To: Iain Lang [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, December 21, 2002 4:22 PM Subject: RE: How long is my piece of string? You need indexes as soon as (or rather just before) they provide a performance difference. Alan -Original Message- From: Iain Lang [mailto:[EMAIL PROTECTED]] Sent: Sunday, 22 December 2002 11:15 AM To: [EMAIL PROTECTED] Subject: How long is my piece of string? . Dear List, I'm using php MySQL for a cycling club website, results, guest-book, events and so on. I've just started and have faithfully created indices all over the place. At present, we have less than 400 records, be they of members, of image URLs, whatever. Each year will, I expect, create an additional 400 records. Am I gilding the lily adding indices for such a small database? Does such a small database really *need* indices, and beyond what number of records might indices provide faster extraction/presentation? I realise how vague a question it is, hence the subject title. Yooors, Iain. -- -- - Most progress has been the result of the actions of unreasonable men. G.B.Shaw. http://www.johnstone-wheelers.co.uk Johnstone-Wheelers - the friendliest cycling club in Scotland! - 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 - 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 - 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: How long is my piece of string?
- Original Message - From: wcb [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Sunday, December 22, 2002 2:23 AM Subject: Re: How long is my piece of string? Hi! I was interested in the answer to this person's question, too! The following response: You need indexes as soon as (or rather just before) they provide a performance difference. Alan seems to lack detail in answering the question, which might be phrased when might indices start making a performance difference?. I would rather reverse the question: is there ever a good reason NOT to use an index? - Mark mysql,query - 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: How long is my piece of string?
Sure. Indices increase the time it takes to add or update a record. They also take up disk space. Also when scanning a database, its quicker ot just read the data records directly than to use an index if most of the data records need to be read anyway. For example, if you had a table with 10,000 records and 9,999 records had fielda=a. Then SELECT * FROM myTable WHERE fieldA=A would be quicker without using an index on fieldA than it would be with an index of fieldA. Mark wrote: - Original Message - From: wcb [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Sunday, December 22, 2002 2:23 AM Subject: Re: How long is my piece of string? Hi! I was interested in the answer to this person's question, too! The following response: You need indexes as soon as (or rather just before) they provide a performance difference. Alan seems to lack detail in answering the question, which might be phrased when might indices start making a performance difference?. I would rather reverse the question: is there ever a good reason NOT to use an index? - Mark mysql,query - 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 - 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: How long is my piece of string?
wcb wrote: seems to lack detail in answering the question, which might be phrased when might indices start making a performance difference?. newbie to indexing talk Well, I'm no wizard or anything, but have just spent the last twos days on this subject here on my project. Anyway, waht I have learned is they make a performance difference immediately, but that can be increases or decreases in performance. The basic rules I have laid out for myself include, only build an index if you 'are' going to be sure you write the SQL to make use of it. This is an area where I'm struggling, but have made a break thru or two. Only index fields with lots of the same data. If every piece of data is different, the index gets to be about as convoluted as the field itself. Indexes slow down writes and updates to the database and take up disk space, so indexing every field in your database is generally not a good idea. I think I read something about mysql getting a bit confused about how best to use the indices if you have lots of them... so, again, carefully create the ones that 'will' for sure be used, and are in a field of like data. In my earlier post regarding the success on my date index stuff, I think the only way the index would be used is if in the WHERE statement, the indexed field was the first to display? and also, maybe it could not be used inside of a function? /newbie to indexing talk So, here I am a newbie to indices talking about something I know little about... Hopefully someone will read this post and correct anything that is in error... So, I'll learn if I've made any errors in my thinking? -- John Hinton - Goshen, VA. http://www.ew3d.com Those who dance are considered insane by those who can't hear the music - 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
