: using the bin-log approach on the master side, how can I
accomplish my replication objectives
Hi Charles,
I believe you would already have bin-log configured, is that right? If not, you
need to.
Secondly, If you think the bin-log generated for the entire stack of
databases/schemas is too big, you
, 2012 3:17 PM
Subject: RE: using the bin-log approach on the master side, how can I
accomplish my replication objectives
Hello Nitin,
Please give Nitin a prize. What a quiet genius she is. Now, I get it. Now, I
can see clearly.
I’ve tried it and it worked.
Thanks so much.
From: Nitin Mehta
.
-Original Message-
From: Nitin Mehta [mailto:ntn...@yahoo.com]
Sent: Wednesday, May 02, 2012 9:25 PM
To: Brown, Charles
Cc: mysql@lists.mysql.com
Subject: Re: using the bin-log approach on the master side, how can I
accomplish my replication objectives
Hi Charles,
I believe you
Tables: db2tb1, db2tb2, db2tb3
Database: db3
Tables: db3tb1, db3tb2, db3tb3
Now, I would like to replicate only these tables that belong to respective
databases:
db1tb1, db2tb2, and db3tb3
My question is: using the bin-log approach on the master side, how can I
accomplish my replication
-rules.html
Hope this helps!
From: Brown, Charles cbr...@bmi.com
To: Rick James rja...@yahoo-inc.com; a.sm...@ukgrid.net
a.sm...@ukgrid.net; mysql@lists.mysql.com mysql@lists.mysql.com
Sent: Thursday, May 3, 2012 8:51 AM
Subject: using the bin-log approach
Hi Charles,
I believe you would already have bin-log configured, is that right? If not, you
need to.
Secondly, If you think the bin-log generated for the entire stack of
databases/schemas is too big, you may want to restrict it using binlog-do-db
BUT that may create problem if you have any
products have made inline (bottom) answering almost
impossible. I, like many others, am forced to used Outlook at the office,
and therefore I had to give up, and now I stick to top-posting :-(
ciao
W.
P.S. the only thing I have not given up yet is not using empty lines to
avoid the feature
- Original Message -
From: Cifer Lee mantia...@gmail.com
it seems none of you hava answered my question.
Yes, that happens :-)
I'm slightly confused as to the difference between user variables and local
variables, though. Are you saying you couldn't just declare @myvar and use
Am 26.03.2012 14:13, schrieb Cifer Lee:
why can't be local variable which declared in DECLARE clause?
what are you speaking about?
keep in mind that we can not read your thoughts
signature.asc
Description: OpenPGP digital signature
is using or
hasn't closed the table properly
Would anyone know what is causing this, and how I can go about fixing it.
Do you think the problem could be on node 1?
Kind Regards
Brent Clark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
was taken
while the tables were open.
Mar 26 14:43:54 xyz-web02 mysqld: 120326 14:43:54 [Warning] Checking table:
'./db/comm_opens'
Mar 26 14:43:54 xyz-web02 mysqld: 120326 14:43:54 [ERROR] 1 client is using
or hasn't closed the table properly
Pretty harmless; a client indeed didn't close
-- Forwarded message --
From: Reindl Harald h.rei...@thelounge.net
Date: Mon, Mar 26, 2012 at 9:17 PM
Subject: Re: why must user variable in EXECUTE USING clause ?
To: Cifer Lee mantia...@gmail.com
would you PLEASE send to the list instead off-list
and put your answer BELOW
, and therefore I had
to give up, and now I stick to top-posting :-(
ciao
W.
P.S. the only thing I have not given up yet is not using empty lines to avoid
the feature (?) of Outlook that glues lines together. This feature can be
disabled/reverted (although this is not widely known)
At 21.38 26/03/2012 +0800
- Original Message -
From: Daevid Vincent dae...@daevid.com
d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP
BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30;
Could you put an index on sg.scene_id? Not sure, but it might help the grouping
be
On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent dae...@daevid.com wrote:
Anyone have any thoughts on how I might optimize this query?
As always, it's all about the indexes. The index it chose on your
main table looks pretty weak. You probably should move those
non-joining columns out of your
-- --- -- --
-- --
--- - --
--
1 SIMPLE d ref
PRIMARY,date_release,STATUS,status_release,dvd_release status_release 1
const2436 USING WHERE; USING TEMPORARY; USING
filesort
1 SIMPLE s ref PRIMARY,dvd_id_2,dvd_id
dvd_id 4
I wouldn't recommend 'playing' with the grant tables instead use the
designated commands.
Anyway keep in mind that if you modify the grant tables manually you have
to force the reload of the privileges in memory by using the 'flush
privileges'.
Not needed if you use GRANT/REVOKE etc.
Cheers
From: Tim Dunphy bluethu...@jokefire.com
... this is just a test environment so getting rid of those users won't have
any meaningful impact...
I think what Paul (who wrote a book on MySQL, by the way) was getting at was
that you risk what database folk call referential integrity issues if
I think what Paul (who wrote a book on MySQL, by the way) was getting at was
that you risk what database folk call referential integrity issues if you
mess with *any* data without knowing where else it is used.
[snip]...
that was really an important post, excellently written!
-Govinda
--
On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote:
hello list,
I have a number of hosts that I would like to delete using a wildcard (%)
symbol.
Here is the query I am using:
mysql delete from mysql.user where user='%.summitnjhome.com';
Couple of things:
* You want to compare your
Hello again list,
Thanks for pointing out where I was making my mistake. I just needed to select
the right field. And this is just a test environment so getting rid of those
users won't have any meaningful impact. Also previewing what you will be
deleting by using a select is great advice I
Hello mysql-list,
I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows 7
using Cygwin), I can start and stop the server (only using mysqld.server -
mysqladmin fails to connect).
I can not connect to the server when I want to use a password (if I don't use
any password
Am 18.11.2011 23:16, schrieb Franck Houssen:
$ mysql -u dummy -p (when I type a real password : dummy, YES... or anything
else)
ERROR 1045 (28000): Access denied for user 'dummy'@'localhost' (using
password: YES)
and if you submit the same post every day you will get the same anser
7 using Cygwin), I can start and stop the server (only using mysqld.server
- mysqladmin fails to connect).
I can not connect to the server when I want to use a password (if I don't
use any password the connection to the server succeeds).
I need client AND server. I followed the on line mysql
binaries for Windows, just use one of them?
On Fri, Nov 18, 2011 at 4:16 PM, Franck Houssen f...@hotmail.com wrote:
Hello mysql-list,
I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows
7 using Cygwin), I can start and stop the server (only using mysqld.server
Hello mysql-list,
I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows 7
using Cygwin), I can start and stop the server (only using mysqld.server -
mysqladmin fails to connect).
I can not connect to the server when I want to use a password (if I don't use
any
(when I type a real password : dummy, YES... or anything
else)
ERROR 1045 (28000): Access denied for user 'dummy'@'localhost' (using
password: YES) $
well this is not a connection problem
your permissions in the mysql-user-tables are wrong
remember that localhost is not the same as 127.0.0.1
@lists.mysql.com
Subject: Re: mysqldump: Got error: 1017: Can't find file:
'./ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Hello Shafi,
Adding to Prabhat alternatives, you can use --force to the mysqldump command
to ignore the errors and continue taking backup.
Regarding the error
:
I have a mysql database of 200G size and the backup fails due to the
foll.
Issue.
mysqldump: Got error: 1017: Can't find file:
'./ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Can someone assist pls.?
$ perror 24
OS error code 24: Too many open files
Folks
I have a mysql database of 200G size and the backup fails due to the foll.
Issue.
mysqldump: Got error: 1017: Can't find file:
'./ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Can someone assist pls.?
Best Rgs,
Shafi AHMED
mysqld to
see if it works.
- Original Message -
From: Shafi AHMED shafi.ah...@sifycorp.com
To: mysql@lists.mysql.com
Sent: Friday, 23 September, 2011 1:42:26 PM
Subject: mysqldump: Got error: 1017: Can't find file:
'./ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK
In the last episode (Sep 23), Shafi AHMED said:
I have a mysql database of 200G size and the backup fails due to the foll.
Issue.
mysqldump: Got error: 1017: Can't find file:
'./ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Can someone assist pls.?
$ perror 24
OS
to the
foll.
Issue.
mysqldump: Got error: 1017: Can't find file:
'./ssconsole/ss_requestmaster.frm' (errno: 24) when using LOCK TABLES
Can someone assist pls.?
$ perror 24
OS error code 24: Too many open files
You need to bump up the max files limit in your OS. It may
This comment has me intrigued:
On 9/21/2011 17:50, John Daisley wrote:
Partitioning is available in the community edition and has been for a
while now. Support is the only real difference and since Oracle took
over the support available in the community is usually faster and better
than you get
...@butterflysystems.co.uk
Cc: John Daisley; Claudio Nanni; Johan De Meersman; Alastair Armstrong;
mysql@lists.mysql.com
Subject: Community Support better than Official Support? (was: Can I Develop
using Community Edition and Deploy onto Enterprise Edition??)
This comment has me intrigued:
On 9/21/2011 17:50, John
; 2011/09/22 13:08 -0400, Jerry Schwartz
The user forum: it has many experienced users, some beta testers, and
(because the product is used world-wide) a response time measured in hours.
What it doesn't have is any presence from the company.
Is n't that what companies nowadays want? Computers
Hi
Please I need some advice.
We are in the process of upgrading from the Free Community Edition of
MySQL on our Live environment to the Enterprise Edition.
Do we need to do the same for my Development environment or can I
continue developing on the Community Ed and then simply deploy any
developing on the Community Ed and then simply deploy any
code, SQL script, etc to the Live Enterprise edition on our live server?
Well... Enterprise edition tends to be a bit behind the community version, so
it's not unthinkable that behaviour might be different.
If you're just using regular queries
deploy any
code, SQL script, etc to the Live Enterprise edition on our live server?
Well... Enterprise edition tends to be a bit behind the community version,
so it's not unthinkable that behaviour might be different.
If you're just using regular queries and stuff, you should be perfectly
you don't need enterprise for the development environment, you can develop
in community version and deploy in enterprise version. but make sure release
version should be same for both.
On Wed, Sep 21, 2011 at 7:06 AM, Alastair Armstrong
alasta...@voxorion.co.za wrote:
Hi
Please I need some
on the Community Ed and then simply deploy any
code, SQL script, etc to the Live Enterprise edition on our live server?
Well... Enterprise edition tends to be a bit behind the community version,
so it's not unthinkable that behaviour might be different.
If you're just using regular queries
Hi,
I have a fairly standard set of queries that I do then I save the resultset as
a CSV and send it off to a couple dozen folks. My understanding is that I can
build an Excel .iqy file that can do the queries (one for each query) and that
I need send that file but once to the users. Does
Hello,
currently i have mod auth_mysql configured and running as plaintext password:
1. how can i generate crypt_des passwords to store in database?
2. how can i get a user log out once the browser tab is closed. seems i have
to close the browser and reopen, then only i am presented login
libmysqlclient (and a
64-bit),
and a C program using the libmysqlclient API which behaves very
differently
depending on which platform it is compiled for. The program is:
system.
You have to compile -m32 and all sort of things.
It is *way* better to compile with pure 64bit.
re,
wh
Am 04.06.2011 02:18, schrieb Alex Gaynor:
I've got a 64-bit Linux system, with a 32-bit libmysqlclient (and a
64-bit),
and a C program using the libmysqlclient API which behaves
I haven't bothered to look for the bug, but it seems to me to be quite
reasonable default behaviour to lock the whole lot when you're dumping
transactional tables - it ensures you dump all tables from the same consistent
view.
I would rather take this up with the ZRM people - it should just
On Mon, 06 Jun 2011 12:44 +0200, Johan De Meersman
vegiv...@tuxera.be wrote:
I haven't bothered to look for the bug, but it seems to me to be quite
reasonable default behaviour to lock the whole lot when you're dumping
transactional tables - it ensures you dump all tables from the same
- Original Message -
From: ag...@airpost.net
Excluding 'performance_schema' appears to eliminate the error. And it
seems does NOT cause a reliability-of-the-backup problem.
Hah, no, backing that up is utterly pointless. Never noticed it doing that.
It's basically a virtual schema
On Mon, 06 Jun 2011 18:54 +0200, Johan De Meersman
vegiv...@tuxera.be wrote:
Excluding 'performance_schema' appears to eliminate the error. And it
seems does NOT cause a reliability-of-the-backup problem.
Hah, no, backing that up is utterly pointless.
that's a useful/final confirmation.
TABL command denied to
user 'root'@'localhost' for table 'cond_instances' when using
LOCK TABLES
-- manual:backup:ERROR: mysqldump did not succeed.
Command used is /usr/bin/mysqldump --opt --extended-insert
--create-options --default-character-set=utf8 --routines
/cache/mysql/mysql.sock --databases
drupal6 performance_schema
/var/mysql-bkup/manual/20110605131003/backup.sql
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
user 'root'@'localhost' for table 'cond_instances' when using
LOCK TABLES
hi,
On Sun, 05 Jun 2011 22:24 +0200, Reindl Harald
h.rei...@thelounge.net wrote:
have you checked you permissions-table if all privileges are active for root
i've got,
mysql show grants for 'root'@'localhost';
the grant statements does nobody interest
maybe use phpmyadmin for a clearer display
mysql select * from mysql.user where user='root' limit 1;
fwiw, others are seeing this. e.g., in addition to the two bugs i'd
already referenced,
http://www.directadmin.com/forum/showthread.php?p=202053
and one
http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with-mysqldump-SELECT-LOCK-TABL-command
claims a solution
Add --skip-add-locks to
hm - bad
i would use a replication slave and stop him for consistent backups
because dumb locks are not really a good solution independent
if this works normally
Am 05.06.2011 23:26, schrieb ag...@airpost.net:
fwiw, others are seeing this. e.g., in addition to the two bugs i'd
already
BTW
WHY is everybody ansering to the list AND the author of the last post?
this reults in get every message twice :-(
Am 05.06.2011 23:26, schrieb ag...@airpost.net:
fwiw, others are seeing this. e.g., in addition to the two bugs i'd
already referenced,
On Sun, 05 Jun 2011 23:30 +0200, Reindl Harald
h.rei...@thelounge.net wrote:
BTW
WHY is everybody ansering to the list AND the author of the last post?
this reults in get every message twice :-(
Reply - sends to ONLY the From == h.rei...@thelounge.net
Reply to all sends to BOTH the From ==
...@thelounge.net
Reply to all sends to BOTH the From == h.rei...@thelounge.net AND the
list.
I suppose if the list manager software, or your client were configured
differently ...
and reply to list replies only to the mailing list
i got burned down from Wietse Venema himself for using
reply all
On Sun, 05 Jun 2011 23:29 +0200, Reindl Harald
h.rei...@thelounge.net wrote:
i would use a replication slave and stop him for consistent backups
because dumb locks are not really a good solution independent
if this works normally
unfortunately, i have no idea what that means.
something's
there is no lock on any production table?
have fun using mysqldump with really hughe databases :-)
a replication slave is synchron, you can stop the slave, copy
the whole datadir and after starting the slave it will
make all changes from the binary log
signature.asc
Description: OpenPGP digital
and reply to list replies only to the mailing list
i got burned down from Wietse Venema himself for using
reply all on the postfix-mailing list some times
by accident
there's no Reply To List command in this client.
i have no idea who Wietse Venema is.
do you REALLY have a need to have
i still have no idea why this is necessary.
there seems to be a but, problem, misconfiguration, etc.
wouldn't it make some sense to try to FIX it, rather than setting up a
completely different server?
perhaps someone with an idea of the problem and its solution will be
able to chime in.
--
Am 05.06.2011 23:52, schrieb ag...@airpost.net:
and reply to list replies only to the mailing list
i got burned down from Wietse Venema himself for using
reply all on the postfix-mailing list some times
by accident
there's no Reply To List command in this client.
so please remove
Am 05.06.2011 23:55, schrieb ag...@airpost.net:
i still have no idea why this is necessary.
take it or not
it is a professional solution which works for
databses with 20 GB every day here with rsync
without interrupt/lock mysqld a second
and it is much faster
there seems to be a but,
),
and a C program using the libmysqlclient API which behaves very differently
depending on which platform it is compiled for. The program is:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
I've got a 64-bit Linux system, with a 32-bit libmysqlclient (and a 64-bit),
and a C program using the libmysqlclient API which behaves very differently
depending on which platform it is compiled for. The program is:
#include stdio.h
#include string.h
#include mysql.h
int main
I'm trying to optimize a query that doesn't seem all that complicated,
however I can't seem to get it to not use a temp table and filesort.
developer@vm_vz_daevid:~$ mysql --version
mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2
EXPLAIN EXTENDED
SELECT
s 2011/05/27 12:26 -0700, Daevid Vincent
[a] the EXTENDED keyword doesn't seem to do anything different? I get the
same columns and results??!
show warnings
2011/05/27 12:26 -0700, Daevid Vincent
In other words, am I wasting my time trying to tweak my
query and indexes here with the idea
I sent this Friday, but it never made it to the list?!
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Friday, May 27, 2011 12:27 PM
To: mysql@lists.mysql.com
Subject: Using where; Using temporary; Using filesort
I'm trying to optimize a query that doesn't seem
Is it ALWAYS possible to fabricate a query/schema in
such a way that MySQL ALWAYS uses the ideal
No. Optimisation is better in 5.6 than in 5.0, though.
Did you try adding multi-column indexes to cover the join and the order
by clause?
'Using where' extra -- you just have to keep
This used to work fine in Mysql 4.3, but no longer works in 5.5.8:
set @txt='needle';
select * from table where field like CONCAT('%',@txt,'%');
--returns the null set. If I substitute like this:
select * from table where field like '%needle%';
it works perfectly (and as it did in 4.x).
Hi, I just tried this on a schema I had laying about and it worked fine:
mysql SET @dude='pilgrim';
Query OK, 0 rows affected (0.00 sec)
mysql SELECT namefield FROM mytable WHERE namefield LIKE
CONCAT('%',@dude,'%');
+---+
| name
I have to increase 'no_of_visits' for 1.
Using UPDATE function is easy:
update visits set no_of_visits=no_of_visits+1 where visitor_id=123
but, how it should be (if possible at all) if I want to use REPLACE
function?
I tried something similar
replace into visitors values ($visitor_id
2011/03/18 17:24 -0500, Afan Pasalic
I have to increase 'no_of_visits' for 1.
Using UPDATE function is easy:
update visits set no_of_visits=no_of_visits+1 where visitor_id=123
but, how it should be (if possible at all) if I want to use REPLACE
function?
I tried something similar
replace
, it is
unclear what we would do that for. It cannot be protection against server
crashes, because that could be resolved by using InnoDB or any other
transactional storage engine. It is not protection against media failures,
because a loss of the binary log because of media failures is equivalent
- Original Message -
From: Chao Zhu zhuc...@gmail.com
One Q: Can mysql binlog use raw device on Linux?
Mmm, good question. Don't really know; but I'm not convinced you'll get huge
benefits from it, either. Modern filesystems tend to perform pretty close to
raw throughput.
From a
Hi,
For the actual question, I agree with the points Johan mentioned. MySQL, to
my knowledge, does not have an option to use raw devices for binary logs. Even
if it had it, it would not have the benefits Chao is seeking. There is indeed
a tradeoff between losing transactions and
, and then submit a second
write request;
In this case, the commit(write throughput) can be enhanced greatly, without
blocking /keeping user wait; In our current test, we are using SAN with
huge cache and each single write only takes 0.3ms(yes very fast, close to
ramdisk i guess); But the sequential
, and then submit a second
write request;
In this case, the commit(write throughput) can be enhanced greatly, without
blocking /keeping user wait; In our current test, we are using SAN with
huge cache and each single write only takes 0.3ms(yes very fast, close to
ramdisk i guess); But the sequential
, then MySQL can continue to submit write request to disk
without waiting for the previous write to complete, and then submit a
second
write request;
In this case, the commit(write throughput) can be enhanced greatly,
without
blocking /keeping user wait; In our current test, we are using SAN
hi, Guys
One Q: Can mysql binlog use raw device on Linux? Can we use asynch IO for
binlog writing? sequential non-qio fsync is slowing our throughput...
Thx
--
Regards
Zhu Chao
Dear all,
Benetl, a free ETL tool for files using MySQL, is out in version 3.7.
You can freely download it at: www.benetl.net
This version brings new controls and messages in the GUI.
This version brings improvements and a bug correction in GUI.
You should update.
You can learn more about ETL
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jess,
I could run the following command to figure out what process is using
the 3306 tcp port :
netstat -nab | findstr 3306
The last colun of the output should be the pid of the process
listeninning on port 3306.
Best Regards.
Luciano
On 11-03
is for stand-alone use.
for the same reason, I also left out the security setup.
After the config process, I used the netstat -nab cmd,
with and without directing to findstr, and found no
mention of port 3306. I can only guess that the earlier
problem--other progs using 3306--was caused by a
residue
Adobe's Photoshop and Bridge also reside on my WinXP
machine. Could they be the cause of failure to config?
During the config process I see a msg that other progs
are using port 3306. Then the config stops--announcing
that mysqld has a problem. Details are:
szAppName : mysqld.exe
Dear all,
Benetl, a free ETL tool for files using MySQL, is out in version 3.7.
You can freely download it at: www.benetl.net
This version brings new controls and messages in the GUI.
This version brings improvements and a bug correction in GUI.
You should update.
You can learn more about ETL
ON px_orders.sales_order_products.order_id = px_orders.sales_orders.id
WHERE
px_orders.sales_order_products.status IN ( 'COMPLETED', 'CANCELED' )
AND px_orders.sales_orders.affiliate_organisation_id = 265;
By explaining this query I am finding that query is not using index in table
Hello!
With the rise of cloud computing and big data, database scalability issues
are becoming critical to the growth and success of many applications. Xeround's
MySQL Cloud Database is an elastic, always-on database that lets you run your
existing MySQL-based application in the cloud.
As we
!, Who do u intend by account manager._I am using
community server 5.1_
On 3 December 2010 02:30, Brian Guy brian@oracle.com
mailto:brian@oracle.com wrote:
Hi Varuna,
Sorry, I do not know the answer to this. Who is your account
manager, and I can ask him/her to find out
2, 2010, at 3:53 PM, Varuna Seneviratna varunasenevira...@gmail.com
wrote:
Hello Brian!
What do you mean!, Who do u intend by account manager.I am using community
server 5.1
On 3 December 2010 02:30, Brian Guy brian@oracle.com wrote:
Hi Varuna,
Sorry, I do not know the answer
Hello Brian!
What do you mean!, Who do u intend by account manager.*I am using
community server 5.1*
On 3 December 2010 02:30, Brian Guy brian@oracle.com wrote:
Hi Varuna,
Sorry, I do not know the answer to this. Who is your account manager, and
I can ask him/her to find out for you
Hey can anyone tell me is my slave is utilizing SSL?
I am unsure why the Master SSL Cipher is not
listed under show slave status.
Here's some info:
mysql show slave status\G
*** 1. row ***
Slave_IO_State: Waiting for master to send
To me it really looks you are SSL replicating.
In my experience If you are replicating in SSL either it works or not,
I dont remember what should be there at Master SSL Cipher,
but I remember I had clear problems when SSL replication was not working.
Claudio
2010/11/10 Paul Nowosielski
|
++-+-+--+-+--+-+--++-+
| 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
| NULL | 396528 | Using where |
++-+-+--+-+--+-+--++-+
And I'm sure the select can fetch records
if your numbers are not
using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for
storage).
Thanks
Aveek
On Nov 9, 2010, at 1:21 PM, Johan De Meersman wrote:
Indexes typically only work on the left-hand-side. Rewrite as
select * from ip_test where startNum = 3061579775
| table | type | possible_keys | key | key_len
| ref | rows | Extra |
++-+-+--+-+--+-+--++-+
| 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL
| NULL | 396528 | Using where
Probably indexes need to be rebuilt using myisamchk after you changed the data
type of the index columns. Apart from that I can't see why your query is not
using the indexes. Is it possible that the cardinality of the column values is
so low that indexes are not being used? You could try
|
+++-+--+--++
| 3061514240 | 3061579775 | 中国| 河南 | | 联通 |
+++-+--+--++
the desc shows it isn't using the index:
mysql desc select * from ip_test where 3061579775 between startNum
Would a compound index on both startnum and endnum be a better choice?
JW
On Tuesday, November 9, 2010, Aveek Misra ave...@yahoo-inc.com wrote:
Probably indexes need to be rebuilt using myisamchk after you changed the
data type of the index columns. Apart from that I can't see why your query
|
+++-+--+--++
| 3061514240 | 3061579775 | 中国| 河南 | | 联通 |
+++-+--+--++
the desc shows it isn't using the index:
mysql desc select * from ip_test where 3061579775 between startNum and endNum
101 - 200 of 3107 matches
Mail list logo
