On 24 Apr 12, at 15:57, mysql-digest-h...@lists.mysql.com wrote:
> From: shawn green
>
>
> On 4/22/2012 11:18 PM, Zhangzhigang wrote:
>> Why does not the mysql developer team to do this optimization?
>
> When the Optimizer is told to sort a result set in the order determined
> by a random val
: RE: Why does the limit use the early row lookup.
>
> > If you are doing Pagination via OFFSET and LIMIT -- Don't. Instead,
> > remember where you "left off".
> > (More details upon request.)
>
> Thanks for your answer.
>
> Can you tell us the be
eft off"?
--- 12年4月24日，周二, Rick James 写道：
> 发件人: Rick James
> 主题: RE: Why does the limit use the early row lookup.
> 收件人: "张志刚" , "mysql@lists.mysql.com"
>
> 日期: 2012年4月24日,周二,上午2:54
> InnoDB or MyISAM?
> PRIMARY KEY (id) is a separate index in My
ort is nearly Order(1),
and the memory is only slightly more than the cost of 10 rows.
> -Original Message-
> From: shawn green [mailto:shawn.l.gr...@oracle.com]
> Sent: Monday, April 23, 2012 5:52 AM
> To: mysql@lists.mysql.com
> Subject: Re: Why does the limit use the ea
y to avoid scanning 110 rows of something (data or index).
If you are doing Pagination via OFFSET and LIMIT -- Don't. Instead, remember
where you "left off". (More details upon request.)
You can trick MySQL into doing "late row lookup" via a "self join":
SELECT
On 4/22/2012 11:18 PM, Zhangzhigang wrote:
> Why does not the mysql developer team to do this optimization?
>
> --- 12年4月20日，周五, Reindl Harald 写道：
>
>> ...
>>
>> because the mysql optimizer until now is really
>> bad in many situations - order by rand() makes a
>> temporary table wil ALL data as
Why does not the mysql developer team to do this optimization?
--- 12年4月20日，周五, Reindl Harald 写道：
> 发件人: Reindl Harald
> 主题: Re: Why does the limit use the early row lookup.
> 收件人: mysql@lists.mysql.com
> 日期: 2012年4月20日,周五,下午3:50
>
>
> Am 20.04.2012 04:29, schrieb 张志刚:
ld [mailto:h.rei...@thelounge.net]
>> Sent: Friday, April 20, 2012 12:50 AM
>> To: mysql@lists.mysql.com
>> Subject: Re: Why does the limit use the early row lookup.
>>
>>
>>
>> Am 20.04.2012 04:29, schrieb 张志刚:
>>> My point is that the limit can
lto:h.rei...@thelounge.net]
> Sent: Friday, April 20, 2012 12:50 AM
> To: mysql@lists.mysql.com
> Subject: Re: Why does the limit use the early row lookup.
>
>
>
> Am 20.04.2012 04:29, schrieb 张志刚:
> > My point is that the limit can use late row lookup: lookup rows af
Am 20.04.2012 04:29, schrieb 张志刚:
> My point is that the limit can use late row lookup: lookup rows after
> checking indexes to optimize the select speed.
>
> But the mysql optimizer do it with the early row lookup: lookup all rows
> before checking indexes when the one fetch c
Hi all,
Given the Social and Political reality in my country (Madagascar),
I am obliged to look for a relocation.
This is my public profile:
http://www.linkedin.com/in/mihaminarakotomandimby
Would you be aware of a position I could fit in?
Thank you.
--
Architecte Informatique chez Blueli
Carlos,
1) To list the users having access to a database,
mysql> SELECT user,host from mysql.db where db = "**";
In your case that would be
mysql> SELECT user,host from mysql.db where db = "*cal*";
2) To grant access to a *NEW *user, you can use GRANT statement along with
IDENTIFIED BY clause,
On Wed, Apr 22, 2009 at 3:11 PM, Carlos Williams wrote:
> On Wed, Apr 22, 2009 at 3:04 PM, Carlos Williams wrote:
>> Now I did create that new database called 'forums' and would like to
>> create a new user who has access only to that specific database from
>> localhost. I can't seem to find the
On Wed, Apr 22, 2009 at 3:04 PM, Carlos Williams wrote:
> Now I did create that new database called 'forums' and would like to
> create a new user who has access only to that specific database from
> localhost. I can't seem to find the command via Google on how I create
> the user and grant access
On Wed, Apr 22, 2009 at 2:45 PM, George Larson
wrote:
>>> Is this what you mean?
>
> SELECT * FROM user;
Yes. That was exactly what I was looking for. Thank you for that info.
Still learning these commands so pardon my ignorance.
Now I did create that new database called 'forums' and would like
On Wed, Apr 22, 2009 at 2:40 PM, Carlos Williams wrote:
> On Wed, Apr 22, 2009 at 2:40 PM, Carlos Williams wrote:
>> On Wed, Apr 22, 2009 at 11:53 AM, Brent Baisley wrote:
>>> All user information is stored in the mysql database. If you want to
>>> see a list of users that have been created, que
On Wed, Apr 22, 2009 at 2:40 PM, Carlos Williams wrote:
> On Wed, Apr 22, 2009 at 11:53 AM, Brent Baisley wrote:
>> All user information is stored in the mysql database. If you want to
>> see a list of users that have been created, query the user information
>> table.
>> select User, Host from my
This seems to be a simple beginer question for MySQL and I have
searched online but I wanted to ask before I really confuse myself.
I have MySQL running on Linux and right now I have created a 2nd
production database:
mysql> show databases;
+-+
| Database
gt; 20081224001, A, B, NULL, READ, 2008-12-24 01:01:03
> My question is if I want to lookup 20081224001 and expect the result to
> be like this:
> transaction id, from, to, message, sent, received
> 20081224001, A, B, stest, 2008-12-24 01:01:01, 2008-12-24 01:01:03
> How to bu
record.
So the records should be something like this:
transaction_id, from, to, message, status, insertdate
20081224001, A, B, stest, SENT, 2008-12-24 01:01:01
20081224001, A, B, NULL, READ, 2008-12-24 01:01:03
My question is if I want to lookup 20081224001 and expect the result to
be like this
oring the value directly in the column, with
the lookup table just used for enforcing a constraint on legal values.
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
> If by "better" you mean "faster" then yes, it probably is marginally
> faster. It would be simpler to just use the actual values you want in
> the lookup columns rather than integers. It might slow down writes a
> little (since the foreign key lookup would
e simpler to just use the actual values you want in
the lookup columns rather than integers. It might slow down writes a
little (since the foreign key lookup would be a string instead of an
integer) but it eliminates joins for reading. The downside is
duplicated data.
- Perrin
--
MySQL General Mail
This is really a MySQL and php question since one of the two options
require programming. Of course I don't think the result would be
different in a different programming language.
I use lookup tables a lot. I have had between 5 to 10 lookup tables for
one table of data. Normall
lumn in a tale that is an
> integer ID used to join to a lookup table. If there is only one Join to
> do it is to do something like this
>
> SELECT t.data, l.group
> FROM table t JOIN lookuptable l USING (groupID)
> WHERE whatever
>
> however if I need to join more than
Chris W wrote:
I often find that I have more than one column in a tale that is an
integer ID used to join to a lookup table. If there is only one Join to
do it is to do something like this
SELECT t.data, l.group
FROM table t JOIN lookuptable l USING (groupID)
WHERE whatever
however if I
I often find that I have more than one column in a tale that is an
integer ID used to join to a lookup table. If there is only one Join to
do it is to do something like this
SELECT t.data, l.group
FROM table t JOIN lookuptable l USING (groupID)
WHERE whatever
however if I need to join
> We have 4 development servers that are fairly configured the same way.
> We have an admin account on each server using %" for the hostname.
> This
> works on 3 of the 4 servers. The 4th server seems to do a lookup and
> since the client machine (i.e. my workstation
We have 4 development servers that are fairly configured the same way.
We have an admin account on each server using %" for the hostname. This
works on 3 of the 4 servers. The 4th server seems to do a lookup and
since the client machine (i.e. my workstation) isn't specifically
specif
Mark, can you provide a sample of a query you're currently using to
retrieve data from this table / joining on this table?
Also, how big is this table? It may not be worth your time to change
things around as this is a very simple structure and should remain
speedy even if it grows pretty large.
hello all this is my first post...I haven't been able to find a way
to half my table size through a primary key relationship that can
exist once.
If I want to relate "person1 (id = 4)" to "person2 (id = 1)"
I have have had good success with the following table below. However,
if i
this lookup table and I am trying get Bob in my table A to match
with Robert in my table B what would the query look like. Just to start
here is kind of what I have been doing so far...
SELECT a.FName a.MName, a.LName, a.Street, a.City, a.State, a.ZIP, a.Phone
FROM TableA as a, TableB as b
WHERE
news <[EMAIL PROTECTED]> wrote on 05/06/2005 06:08:18 PM:
> hi,
> When designing data it is common to have lookup tables such
> animal_type : dog=1, cat=2,bird=3 etc
>
> And then in other tables to refer to animals by their number 1, 2
> or 3. This is memory and
>
On Sat, 7 May 2005 09:35:21 +0100, wrote:
>One issue you have to consider is how the data is entered. If the user
>selects from a drop down list then this is ok but you need to plan how the
>drop down list is compiled. If not then you need to thinks about spelling
>errors, language issues etc.
-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of zzapper
Sent: 06 May 2005 23:08
To: mysql@lists.mysql.com
Subject: Data Design : Numeric or keyword lookup values?
hi,
When designing data it is common to have lookup tables such animal_type :
dog=1, cat=2,bird=3 etc
And then in other
hi,
When designing data it is common to have lookup tables such animal_type :
dog=1, cat=2,bird=3 etc
And then in other tables to refer to animals by their number 1, 2 or 3. This
is memory and
presumably speed efficient. Howver not much fun for humans who are
"reading/debugging"
cc: <[EMAIL PROTECTED]>
Fax to:
05/18/2004 08:51 Subject: lookup
I asked a question earlier about how to handle lookup tables, I think this
is a little more clear as to what I was trying to ask.
if I want to relate a contact to an organization, I know I can create a
table that contains contact_ids and org_ids.
But, how do I enter that information gracefully
I just learned that MySql 4 comes installed on OS X server. There is this
little admin app that allows you to enable it, I was not used to this, but
it seemed to work, I was able to login and see the 'test' table.
I then proceeded to set up the password.
/usr/local/mysql/bin/mysqladmin -u root pa
gt;
Sent: Sunday, October 05, 2003 1:08 AM
Subject: ip range lookup
> I have a table that maps ip-ranges to countries: each record consists of 2
> ip numbers (unsigned int's) and the country in which all ip's between
those
> two are located.
> How should I setup the table t
Hi Willem,
I don't *think* MySQL optimizes BETWEEN like that to use an index.
Have you tried this?:
SELECT * FROM ipcountry WHERE ip1 >= 123456789 AND ip2 <= 123456789;
Matt
- Original Message -
From: "Willem Bison"
Sent: Saturday, October 04, 2003 6:08 PM
Sub
I have a table that maps ip-ranges to countries: each record consists of 2
ip numbers (unsigned int's) and the country in which all ip's between those
two are located.
How should I setup the table to have fast ip lookups ?
Making a primary key of the 2 ip's and doing a 'select .. between ip1 and
ip
On Fri, 29 Aug 2003 01:02:57 -0400 (EDT)
"Jordan Morgan" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm very new to MySQL. I normally use MS Access but my project needs
> MySQL. I'm able to set a field(categoryID) in table A(product) a lookup
> field to anot
Hi,
I'm very new to MySQL. I normally use MS Access but my project needs
MySQL. I'm able to set a field(categoryID) in table A(product) a lookup
field to another table(category) in Access but I can't find anywhere that
teaches me how to do that in MySQL.
I'm managing
Does the FIND_IN_SET() function mentioned in the manual have any
performance bonuses over doing a LIKE search for a SET fields? I'm
trying to decide between using a SET or a separate lookup table at the moment,
but data needs to be searchable and could have a lot of values, so it
needs
using the CREATE FUNCTION command to add the avaiable UDF's
until I get to lookup and reverse_lookup. On these two, I get the
following error message:
ERROR 1127: Can't find function 'reverse_lookup' in library
ERROR 1127: Can't find function 'lookup' in li
# [EMAIL PROTECTED] / 2003-06-28 16:56:13 +0200:
> I use mySQL on HP/UX 11.i - unfortunateley I am unable to lookup the
> used version. What is the command to do that? (according to mysql.info
> it is Version 3.23.42. )
SELECT VERSION();
--
If you cc me or remove the list(s) comple
Hello,
I use mySQL on HP/UX 11.i - unfortunateley I am unable to lookup the
used version. What is the command to do that? (according to mysql.info
it is Version 3.23.42. )
Are foreign keys and referencial integrity supported by MySQL Version
3.23.42. ?
If yes - why is the second "insert
nt: Saturday, October 12, 2002 12:52 AM
Subject: Mysqld DNS lookup causes crash
> >Description:
> Server version 3.23.49-log
> Protocol version10
> OS Linux atlas 2.4.18-5smp #1
>
> Mysqld two days ago began to crash on accepting non-local mysql
connections.
: Mysqld crashing on DNS lookup
>Severity: serious
>Priority: medium
>Category: mysql
>Class:
>Release: mysql-3.23.49 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for redhat-linux-gnu on i386
Copyright (C) 2000 MySQL
sajiddalvi,
Sunday, July 07, 2002, 8:04:02 PM, you wrote:
s> Is there a way to check which my sql user (who has logged into the database)
s> has inserted a row in a table?
s> I could add a "logged_in_user" column but that seems redundant.
Take a look at functions: USER(), SYSTEM_USER(), SESSION_
Pada Sun, 7 Jul 2002 13:04:02 -0400
"sajiddalvi" <[EMAIL PROTECTED]> menulis :
> Is there a way to check which my sql user (who has logged into the database)
> has inserted a row in a table?
> I could add a "logged_in_user" column but that seems redundant.
you can set your mysqld to log all acti
Is there a way to check which my sql user (who has logged into the database)
has inserted a row in a table?
I could add a "logged_in_user" column but that seems redundant.
---
Introducing NetZero Long Distance
Unlimited Long Distance only $29.95/ month!
Si
binary" argument.
If you would like me to send binary.conf file, please let me know.
Keith C. Ivey wrote:
> On 28 Jun 2002, at 14:27, Mark Hennessy wrote:
>
>
>>I have noticed after migrating my database from mSQL to MySQL that
>>queries are providing lookup results in a
This set of responses is getting away from the original question. The
lookup "data type" in Microsoft Access is not a data type at all. It
creates a join with another table underneath the surface.
If you try to implement this with ENUM and Alter Table you will destroy
the existing
17, 2002 3:32 PM
To: Andrew Hazen; [EMAIL PROTECTED]
Subject: RE: LOOKUP
Hi,
Your solution would work if the set of the values you'll get from one
table
is primary key.. If it is not a primary key, i.e. allowing duplicate
values,
then everytime there is an insertion to the table you will ha
ginal Message-
From: Andrew Hazen [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 17, 2002 1:58 PM
To: 'Gurhan Ozen'; 'Simon Tienery'; [EMAIL PROTECTED]
Subject: RE: LOOKUP
That's what I thought too. A workaround occurs to me though, but I'm
not sure if it would
That's what I thought too. A workaround occurs to me though, but I'm
not sure if it would muck things up.
At any point where you make a change to the table you want to reference
as the "lookup" (as in Access), you could include another sql command to
alter the table wit
or it.
Sincerely,
Gurhan
-Original Message-
From: Andrew Hazen [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 17, 2002 12:54 PM
To: 'Gurhan Ozen'; 'Simon Tienery'; [EMAIL PROTECTED]
Subject: RE: LOOKUP
Gurhan,
I think Simon was looking for a way to make the enum d
Gurhan,
I think Simon was looking for a way to make the enum dynamic from
another table. ??
Andrew Hazen
-Original Message-
From: Gurhan Ozen [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 17, 2002 10:22 AM
To: Simon Tienery; [EMAIL PROTECTED]
Subject: RE: LOOKUP
Hi ..
You
told that Access has a data type that does this called lookup,
but I only want to use MySQL.
I couldn't locate the answer in the manual and I'd appreciate it if someone
can explain how this can be achieved / worked around.
Actually, I've been looking pretty closely at a couple of other tables I
wanted to implement as HEAP tables -- and I didn't even think about moving
my little lookup tables into HEAP. :)
Thanks!
--jeff
- Original Message -
From: "Jeremy Zawodny" <[EMAIL PROTECT
On Wed, Feb 27, 2002 at 09:14:51PM -0800, Jeff Kilbride wrote:
>
> Is there a rule of thumb for small tables and whether they should be
> indexed? I have several small, two column lookup tables with few
> rows (100-300) and some very small tables (< 10 rows). I don't plan
&g
ruary 28, 2002, 7:14:51 AM, you wrote:
>>
>> JK> Is there a rule of thumb for small tables and whether they should be
>> JK> indexed? I have several small, two column lookup tables with few rows
>> JK> (100-300) and some very small tables (< 10 rows). I don't
Hi Egor,
Thanks for the reply. Does it actually hurt to index them on lower volumes
of traffic? Or is it neglible?
--jeff
- Original Message -
From: "Egor Egorov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, February 28, 2002 8:40 AM
Subject: Look
Jeff,
Thursday, February 28, 2002, 7:14:51 AM, you wrote:
JK> Is there a rule of thumb for small tables and whether they should be
JK> indexed? I have several small, two column lookup tables with few rows
JK> (100-300) and some very small tables (< 10 rows). I don't plan on inde
Is there a rule of thumb for small tables and whether they should be
indexed? I have several small, two column lookup tables with few rows
(100-300) and some very small tables (< 10 rows). I don't plan on indexing
the very small ones, but what about the others?
Thanks,
--jeff
s
At 22:53 06.09.01, you wrote:
>mysql> SELECT receiver_id, associate_of, user_name FROM receivers;
>+-+--+-+
>| receiver_id | associate_of | user_name |
>+-+--+-+
>| 1 |0 | arnold |
>| 2
Balazs Szemes wrote:
> Hi,
> It is a recursive table design, meaning that a person can have a boss. The
> boss' id is stored in the associate_of column. Eg. elmer is barney's
> associate, barney is boss of elmer, and diana
>
> Let's say I only know the user_name 'barney', and I would like to sele
Hi,
Please help me with a query. I looked in the documentation w/o success.
Consider this table:
mysql> SELECT receiver_id, associate_of, user_name FROM receivers;
+-+--+-+
| receiver_id | associate_of | user_name |
+-+--+---
I find myself having to deal with lookup tables to preserve
space and speed up queries. Something like:
create table test (
somedate DATE,
long_column_id INT UNSIGNED,
);
create table longcol_lookup (
long_column_id_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
long_column CHAR(128
71 matches
Mail list logo
