Re: MySQL Indexes

2011-10-07 Thread Tompkins Neil
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

Re: mysqldiff resurrected and 0.43 released

2011-10-07 Thread Rik Wasmus
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

Re: 4 minute slow on select count(*) from table - myisam type

2011-10-07 Thread Johan De Meersman
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

RE: How MyISAM handle auto_increment

2011-10-07 Thread Jerry Schwartz
-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

Re: MySQL Indexes

2011-10-07 Thread Brandon Phelps
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

Re: MySQL Indexes

2011-10-07 Thread 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

Re: MySQL Indexes

2011-10-07 Thread Michael Dykman
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

Re: MySQL Indexes

2011-10-07 Thread Reindl Harald
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

Re: MySQL Indexes

2011-10-07 Thread Michael Dykman
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,

Re: MySQL Indexes

2011-10-07 Thread Neil Tompkins
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

Re: MySQL Indexes

2011-10-07 Thread Neil Tompkins
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

Re: MySQL Indexes

2011-10-07 Thread Michael Dykman
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

FW: MySQL Indexes

2011-10-07 Thread Jerry Schwartz
-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

Re: FW: MySQL Indexes

2011-10-07 Thread Brandon Phelps
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?

Re: MySQL Indexes

2011-10-07 Thread mos
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

MySQL anemic GIS support

2011-10-07 Thread René Fournier
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

Re: MySQL anemic GIS support

2011-10-07 Thread Michael Dykman
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