Hi,
On 28-8-2011 4:08, shawn wilson wrote:
On Sat, Aug 27, 2011 at 17:33, Arthur Fuller wrote:
I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column?
A NULL 'value' is special in most
On Sat, Aug 27, 2011 at 17:33, Arthur Fuller wrote:
> I agree 110%. It is completely pointless to index a column with that amount
> of NULLs. In practical fact I would go further: what is the point of a
> NULLable column? I try to design my tables such that every column is NOT
> NULL. In practice
It is a general rule that indexes for columns with low cardinality are not
worth it, often making queries more expensive than they would be without
said index. binary columns all suffer from this.
- michael dykman
On Sat, Aug 27, 2011 at 4:52 PM, Dave Dyer wrote:
>
> The "innocuous change" w
I agree 110%. It is completely pointless to index a column with that amount
of NULLs. In practical fact I would go further: what is the point of a
NULLable column? I try to design my tables such that every column is NOT
NULL. In practice this is not realistic, but I try to adhere to this
principle
Hi,
On 27-8-2011 22:52, Dave Dyer wrote:
The "innocuous change" was to add an index for "is_robot" which is true
for 6 out of 20,000 records and null for the rest.
Not useful to add an index for that. I also wonder why the value is null
(meaning: unknown, not certain) for almost all records.
The "innocuous change" was to add an index for "is_robot" which is true
for 6 out of 20,000 records and null for the rest.
My complaint/question/observation is not how to optimize the query
that went awry, but to be alarmed that a venerable and perfectly
serviceable query, written years ago and
Hi,
On 27-8-2011 1:28, Dave Dyer wrote:
Can you post the EXPLAIN EXTENDED output for your before and after queries?
also, have you recently run an ANALYZE TABLE on the tables?
What was the result of ANALYZE TABLE?
What is the engine of the tables involved?
// before
Used keys:
p2.NULL,
BTW, the query on the database with the added index doesn't take
forever, it takes a mere 51 minutes (vs. instantaneous).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
BTW, the query on the database with the added index doesn't take
forever, it takes a mere 51 minutes (vs. instantaneous).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
>
>
>Can you post the EXPLAIN EXTENDED output for your before and after queries?
>also, have you recently run an ANALYZE TABLE on the tables?
// before
mysql> explain extended select
p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
-> f
In the last episode (Aug 26), Dave Dyer said:
> This is a cautionary tale - adding indexes is not always helpful or
> harmless. I recently added an index to the "players" table to optimize a
> common query, and as a consequence this other query flipped from innocuous
> to something that takes infi
This is a cautionary tale - adding indexes is not always helpful or harmless.
I recently added an index to the "players" table to optimize a common query,
and as a consequence this other query flipped from innocuous to something that
takes infinite time.
select
p1.player_name,g.score1,g.tim
* [EMAIL PROTECTED]
> In order to remove the first character '0' of the field 'number',
> I want to use the following sql query to select the field from my
> database.
>
> select right(number,
> if(locate('0',length(number))=1,length(number)-1,length(number)))
> as 'no_zero' from mydatabase or
On Fri, 14 Jun 2002, [GB2312] ³Â׿ wrote:
> mysqlmysql 3.23.41
> phpMyAdmin 2.2.3
> os :Linux server 2.4.9-31
>
> In order to remove the first character '0' of the field 'number',
> I want to use the following sql query to select the field from my database.
>
> select right(number,
>if(locate(
mysqlmysql 3.23.41
phpMyAdmin 2.2.3
os :Linux server 2.4.9-31
In order to remove the first character '0' of the field 'number',
I want to use the following sql query to select the field from my database.
select right(number, if(locate('0',length(number))=1,length(number)-1,length(number)))
as '
mysqlmysql 3.23.41
phpMyAdmin 2.2.3
os :Linux server 2.4.9-31
In order to remove the first character '0' of the field 'number',
I want to use the following sql query to select the field from my database.
select right(number, if(locate('0',length(number))=1,length(number)-1,length(number)))
as '
16 matches
Mail list logo
