Re: MySQL Indexes
Is it normal practice for a heavily queried MYSQL tables to have a index file bigger than the data file ? On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman mdyk...@gmail.com wrote: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.commdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: mysqldiff resurrected and 0.43 released
Looks very nice, Ill check it out next week. Thanks for the work! -- Rik Wasmus After a very long hiatus from maintainership (several years), I have finally released a new version of MySQL-Diff, the CPAN module suite which also contains mysqldiff, a CLI-based frontend tool for comparing the table schema of a pair of MySQL databases. Its output is a sequence of MySQL statements (CREATE/ALTER/DROP TABLE/COLUMN etc.), which if applied to the first database of the pair, will make its schema match that of the second. The web page is here: http://adamspiers.org/computing/mysqldiff/ and the manual page is here: http://search.cpan.org/dist/MySQL-Diff/bin/mysqldiff There are still a few open bugs in the tracker which need ironing out, mostly due to the evolution of MySQL itself since I first wrote this utility in 2000. Nevertheless I hope that some of you will already be able to benefit from the new life I'm trying to breathe into this little project. github forks are of course welcome too ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
Good to see the issue has been solved. What I noticed in the mysqltuner output, is that you may want to enlarge your table_cache and open files limit before you run into problems there. - Original Message - From: Johnny Withers joh...@pixelated.net I haven't used MYISAM in a long time, so i'm not sure about this but.. is the INSERT locked due to the SELECT queries that have been running for so long? And are the rest of the selects (with 8s running time) locked by the INSERT? Yes, because MyISAM doesn't have a mechanism for keeping multiple concurrent consistent views on a table, SELECT also locks the table for writes. There is one important and occasionally useful exception, though: if there are no holes in the table (ie, if you haven't done any deletes) the engine will allow inserts to append to the table while selects are happening. -- 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: How MyISAM handle auto_increment
-Original Message- From: Lucio Chiappetti [mailto:lu...@lambrate.inaf.it] Sent: Thursday, October 06, 2011 3:18 AM To: Jerry Schwartz Cc: Mysql List Subject: RE: How MyISAM handle auto_increment On Wed, 5 Oct 2011, Jerry Schwartz wrote: Can't you use CREATE TABLE LIKE and then reset the auto-increment value? Thanks. Since when does create table like exist? I was unaware of it, but I see it exists in mysql 5.1. The tricks I described worked since 3.x or thereabouts. [JS] I don't know when it was introduced. I never used anything before 4.0, and I don't remember when I first used it that command. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) Citizens entrusted of public functions have the duty to accomplish them with discipline and honour [Art. 54 Constitution of the Italian Republic] For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Indexes
This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Indexes
When a query selects on field_a and field_b, that index can be used. If querying on field_a alone, the index again is useful. Query on field_b alone however, that first index is of no use to you. On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote: This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@** googlemail.com neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdykman@**gmail.com mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.comhttp://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: MySQL Indexes
How heavily a given table is queried does not directly affect the index size, only the number and depth of the indexes. No, it is not that unusual to have the index file bigger. Just make sure that every index you have is justified by the queries you are making against the table. - md On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil neil.tompk...@googlemail.comwrote: Is it normal practice for a heavily queried MYSQL tables to have a index file bigger than the data file ? On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman mdyk...@gmail.com wrote: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.commdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: MySQL Indexes
but could this not be called a bug? Am 07.10.2011 18:08, schrieb Michael Dykman: When a query selects on field_a and field_b, that index can be used. If querying on field_a alone, the index again is useful. Query on field_b alone however, that first index is of no use to you. On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote: This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@** googlemail.com neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdykman@**gmail.com mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.comhttp://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: MySQL Indexes
No, I don't think it can be called. It is a direct consequence of the relational paradigm. Any implementation of an RDBMS has the same characteristic. - md On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald h.rei...@thelounge.netwrote: but could this not be called a bug? Am 07.10.2011 18:08, schrieb Michael Dykman: When a query selects on field_a and field_b, that index can be used. If querying on field_a alone, the index again is useful. Query on field_b alone however, that first index is of no use to you. On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote: This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@** googlemail.com neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdykman@**gmail.com mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.com http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: MySQL Indexes
Can you give more information as to why the second index would be of no use ? On 7 Oct 2011, at 18:24, Michael Dykman mdyk...@gmail.com wrote: No, I don't think it can be called. It is a direct consequence of the relational paradigm. Any implementation of an RDBMS has the same characteristic. - md On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald h.rei...@thelounge.netwrote: but could this not be called a bug? Am 07.10.2011 18:08, schrieb Michael Dykman: When a query selects on field_a and field_b, that index can be used. If querying on field_a alone, the index again is useful. Query on field_b alone however, that first index is of no use to you. On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote: This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@** googlemail.com neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdykman@**gmail.com mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.com http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Indexes
Do you have any good documentation with regards creating indexes. Also information for explain statement and what would be the desired result of the explain statement? On 7 Oct 2011, at 17:10, Michael Dykman mdyk...@gmail.com wrote: How heavily a given table is queried does not directly affect the index size, only the number and depth of the indexes. No, it is not that unusual to have the index file bigger. Just make sure that every index you have is justified by the queries you are making against the table. - md On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Is it normal practice for a heavily queried MYSQL tables to have a index file bigger than the data file ? On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman mdyk...@gmail.com wrote: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: MySQL Indexes
The second index you specified '(field_b, field_a)' would be usable when querying on field_b alone, or both fields in conjunction. This particular index is of no value should you be querying 'field_a' alone. Then that first index '(field_a, field_b)' would apply. - md On Fri, Oct 7, 2011 at 2:55 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Can you give more information as to why the second index would be of no use ? On 7 Oct 2011, at 18:24, Michael Dykman mdyk...@gmail.com wrote: No, I don't think it can be called. It is a direct consequence of the relational paradigm. Any implementation of an RDBMS has the same characteristic. - md On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald h.rei...@thelounge.net wrote: but could this not be called a bug? Am 07.10.2011 18:08, schrieb Michael Dykman: When a query selects on field_a and field_b, that index can be used. If querying on field_a alone, the index again is useful. Query on field_b alone however, that first index is of no use to you. On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote: This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@** googlemail.com neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdykman@**gmail.com mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.com http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman -
FW: MySQL Indexes
-Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, October 07, 2011 12:21 PM To: mysql@lists.mysql.com Subject: Re: MySQL Indexes but could this not be called a bug? [JS] No. Think of two telephone books: one is sorted by first name, last name and the other is sorted by last name, first name. (Those are like your two keys, f1/f2 and f2/f1.) If you want to find someone by their first name, you use the first book. If you want to find somebody by their last name, you use the second book. If you want to find someone by their last name, the first book (key f1/f2) is useless. If you want to find someone by their first name, the second book (f2/f1) is useless. Does that help explain it? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com Am 07.10.2011 18:08, schrieb Michael Dykman: When a query selects on field_a and field_b, that index can be used. If querying on field_a alone, the index again is useful. Query on field_b alone however, that first index is of no use to you. On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps bphe...@gls.com wrote: This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext- natural-language.html http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext- natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@** googlemail.com neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdykman@**gmail.com mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.comhttp://lists.mysql.com/m ysql?unsub=mdyk...@gmail.com -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: FW: MySQL Indexes
That cleared it up for me. Thanks! On 10/07/2011 03:06 PM, Jerry Schwartz wrote: -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, October 07, 2011 12:21 PM To: mysql@lists.mysql.com Subject: Re: MySQL Indexes but could this not be called a bug? [JS] No. Think of two telephone books: one is sorted by first name, last name and the other is sorted by last name, first name. (Those are like your two keys, f1/f2 and f2/f1.) If you want to find someone by their first name, you use the first book. If you want to find somebody by their last name, you use the second book. If you want to find someone by their last name, the first book (key f1/f2) is useless. If you want to find someone by their first name, the second book (f2/f1) is useless. Does that help explain it? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com Am 07.10.2011 18:08, schrieb Michael Dykman: When a query selects on field_a and field_b, that index can be used. If querying on field_a alone, the index again is useful. Query on field_b alone however, that first index is of no use to you. On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelpsbphe...@gls.com wrote: This thread has sparked my interest. What is the difference between an index on (field_a, field_b) and an index on (field_b, field_a)? On 10/06/2011 07:43 PM, Nuno Tavares wrote: Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykmanmdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext- natural-language.html http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** natural-language.htmlhttp://dev.mysql.com/doc/refman/5.5/en/fulltext- natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neilneil.tompkins@** googlemail.comneil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman -mdyk...@gmail.commdykman@**gmail.commdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.comhttp://lists.mysql.com/m ysql?unsub=mdyk...@gmail.com -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: MySQL Indexes
At 01:58 PM 10/7/2011, you wrote: Do you have any good documentation with regards creating indexes. Also information for explain statement and what would be the desired result of the explain statement? This might help: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html http://www.sitepoint.com/optimizing-mysql-application/ http://hackmysql.com/case2 There is one more advantage about compound indexes like index on (field_a, field_b). If you are retrieving just field_a and field_b, in a select statement : select field_a, field_b from table1 where field_a=abc even though you only references field_a in the Where clause, it of course uses that index to find rows with field_a=abc, but it also retrieves field_b from the SAME index so MySQL doesn't have to go to the data file to get field_b. This can dramatically reduce disk I/O in heavily used queries and occasionally you may want to create a compound index even though the second field won't be used in a Where clause. There is a yin and yang approach to creating indexes. Newbies will try and index all possible columns that are used in a Where clause which results in a huge index file and very slow table updates. The more indexes you have on a table, the slower it takes to add or update a row. You really only want to index the columns of the most frequent queries. As to which fields to index, on a test database I would remove all indexes from the table except for the primary keys and have the slow query log turned on. Run your queries for an hour and examine the slow query log to see which queries are slow. Copy and paste the slow Select query to a MySQL administrator like SqlYog Community Edition v9.2 (http://code.google.com/p/sqlyog/downloads/list) and do an explain on the query to see what indexes it is (not) using. Then alter the table and add an index to try and speed up the query. You may have to repeat this several times to finally get the proper index defined. Remember to Reset Query Cache between tests. Only by judiciously adding indexes one by one and testing the performance, will you have the proper yin and yang so your tables are in harmony. Mike (If you can't achieve harmony, then buy more hardware.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL anemic GIS support
Anyone have any idea on if/when MySQL will get real GIS support? http://mysqldbnews.blogspot.com/2007/10/does-mysql-gis-make-grade.html …is what I'm referring to. Specifically, the factor that many functions are quietly replaced with MBRContains(). This makes it, for example, not possible to determine with certainty (in SQL) if a point lies within a non-rectangular polygon. I ask because I'm looking at moving a big part of our applications to Postgresql, and, well, I'd rather not have to. …Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL anemic GIS support
Somebody feel to jump in and contradict me here, but I have never had any love from the MySQL GIS stack. For the very few functions it does support, the performance has been abysmal and I generally find myself hacking together UDFs against columns of FLOAT and avoiding POINT altogether. - md On Fri, Oct 7, 2011 at 10:41 PM, René Fournier m...@renefournier.com wrote: Anyone have any idea on if/when MySQL will get real GIS support? http://mysqldbnews.blogspot.com/2007/10/does-mysql-gis-make-grade.html …is what I'm referring to. Specifically, the factor that many functions are quietly replaced with MBRContains(). This makes it, for example, not possible to determine with certainty (in SQL) if a point lies within a non-rectangular polygon. I ask because I'm looking at moving a big part of our applications to Postgresql, and, well, I'd rather not have to. …Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you.
