MySQL 5 + Niagara?

[johanboy13]

Hi,

Does anyone know which is the best binary version of MySQL 5 to use for a 
SunFire T2000 (with an 8 core Niagara processor). Does the Ultra-sparc-version 
work?

/Johan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to Find the Max/Min from Multiple Columns (in each row)

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Henry Chang <[EMAIL PROTECTED]> writes:

> Hello everyone,
> I have a table where measurement values are collected in mulitple columns.

> Table Schema
> ==
> ID, measurement_01, measurement_02, measurement_03
> ==
> 1, 300, 350, 325(max is 350, min is 300)
> 2, 225, 275, 400(max is 400, min is 225)
> 3, 100, 500, 300(max is 500, min is 100)
> ==

> My question is that for each row, what's the sql query that determine the
> max value and the min value from all the columns??

[Others told you the answer for that]

> (I realize that each measurment should be it's own row, but I must use the
> table as it is.)

How about the following?

  CREATE VIEW mytblv AS
  SELECT id, measurement_01 AS measurement
  FROM mytbl
  UNION
  SELECT id, measurement_02
  FROM mytbl
  UNION
  SELECT id, measurement_03
  FROM mytbl


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Out of Range value adjusted?

[Gleb Paharenko]

Hello.

If you still want to use the decimal(3,2) data type for your table
(even after excellent explanations by Shawn), check that the pure
instance of MySQL doesn't use STRICT sql mode. See:
  http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html


Kerry Frater wrote:
> I am trying to test some code on the new 5.0 version and am getting
> problems.
> 
> I currently have two MYSQL's running on two differing machines. Both have
> the same schema set up by the my own program. The difference is that Machine
> 1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a fresh
> installation of version 5.
> 
> My Data insertion code works perfectly well with the Version 5 instance that
> was upgraded from version 4, but fails on the pure version 5. The
> Administrator interface tells me that the tables are OK and I cannot see any
> difference between the V4 upgraded to 5 table against the pure V5 install.
> 
> The code being used is
> INSERT INTO Invs
> (InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
> VatRate)
> VALUES
>  ('1234#6','19991016','C','19990731','\"Orig Bill
> £728.50\"',364.68,'A',17.50);
> 
> The table columns are set to
> char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2). The
> table is MyISAM.
> 
> There error produced on the pure version 5 installation is:
> "Out of Range value adjusted for column VATRate at row 1"
> and then quits. I have let the installation of MySQL use its defaults and is
> of charset "latin1" if it is an issue. The above statement does not error on
> the upgraded version of MySQL but adds the row with no problem.
> 
> The set up program is using a function
>with SQLBatch.SQL do
>begin
>   Add('CREATE TABLE Invs (');
>   Add('InvRef char(12) default NULL,');
>   Add('InvDate date,');
>   Add('OutgoingRef char(1),');
>   Add('OutgoingRef2 date,');
>   Add('BillDescription char(40),');
>   Add('BillAmount decimal(12,2),');
>   Add('VATCode char(1),');
>   Add('VATRate decimal(3,2),');
>   Add('  index (OutgoingRef2)');
>   Add(') TYPE=MyISAM;');
>end;
>SQLBatch.ExecSql;
> 
> This seems to work fine. Any ideas on why it works with one and not the
> other?
> 
> Kerry
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Optimizing Tables - Trimming Data

[Shaun]

Hi,

I am trying to optimize my data and am currently looking at indexes etc. 
Someone has suggested the following as well:

1. Ensure that there is no whitespace at the beginning or end of entries
2. Use 1 or 0 instead of yes or no.

If point 1 is true then is there a way to trim all whitespace data? The 
problem I see with point 2 is it makes querying the data more awkward to 
replace every 1 with a yes and 0 with a no.

I would be most grateful for your comments on this. 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update a Blob field using UPDATE

[Gleb Paharenko]

Hello.

Have you applied mysql_real_escape_string to your BLOB variable first?
See:
  http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html

I do not see the quotes around mystring$ as well.


Kerry Frater wrote:
> I am importing data from a non MySQL table into MySQL.
> 
> In the table there is a text field of up to length 4000 chars. I have
> defined the column as "blob" in the MySQL table.
> I can read the text field of the source table into a variable e.g.
> mystring$. The MySQL table has been set, except for this data.
> 
> I thought to use
>   SQLString = "UPDATE TheTable SET Notes = " + mystring$ + " WHERE
> TheTableRef = '" + Myref$ + "';"
> 
> I get error:
> You have an error in the SQL syntax
> 
> I have tried to search the manual for an example of updating a blob column
> from a variable and cannot find one. I don't want to save the content of the
> var to disk and then "load from file" because of the time it takes.
> 
> Do I have another option?
> 
> Kerry
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimizing Tables - Trimming Data

[Gleb Paharenko]

Hello.

You will get much more help from the list if you provide the current
table structure, short description of the information which is stored
in that fields, and the query you want to optimize (most probably it
takes much more time than others).

> If point 1 is true then is there a way to trim all whitespace data?

There are LTRIM and RTRIM functions. See:
  http://dev.mysql.com/doc/refman/5.0/en/string-functions.html





Shaun wrote:
> Hi,
> 
> I am trying to optimize my data and am currently looking at indexes etc. 
> Someone has suggested the following as well:
> 
> 1. Ensure that there is no whitespace at the beginning or end of entries
> 2. Use 1 or 0 instead of yes or no.
> 
> If point 1 is true then is there a way to trim all whitespace data? The 
> problem I see with point 2 is it makes querying the data more awkward to 
> replace every 1 with a yes and 0 with a no.
> 
> I would be most grateful for your comments on this. 
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Optimizing Tables - Trimming Data

[Shaun]

Sorry guys,

here is a table description, query and result:

SHOW CREATE TABLE Properties;

Properties |CREATE TABLE `Properties` (
  `Property_ID` int(11) NOT NULL auto_increment,
  `Insertion_Date` date default NULL,
  `Status` varchar(20) default NULL,
  `Uploader_ID` int(11) default NULL,
  `Approver_ID` int(11) default NULL,
  `Property_Name_Or_Number` varchar(50) default NULL,
  `Address_Line_1` varchar(50) default NULL,
  `Address_Line_2` varchar(50) default NULL,
  `City` varchar(50) default NULL,
  `County` varchar(50) default NULL,
  `Postcode` varchar(12) default NULL,
  `Asking_Price` int(11) default NULL,
  `Date_On_Market` date default NULL,
  `Sale_Price` int(11) default NULL,
  `Exchange_Date` date default NULL,
  `Tenure_ID` int(11) default NULL,
  `Years_On_Lease` int(11) default NULL,
  `Tax_Band_ID` int(11) default NULL,
  `Age_ID` int(11) default NULL,
  `Type_ID` int(11) default NULL,
  `Number_Of_Bedrooms` int(11) default NULL,
  `Number_Of_Bathrooms` int(11) default NULL,
  `Number_Of_Receptions` int(11) default NULL,
  `Number_Of_Kitchens` int(11) default NULL,
  `Desirability_Of_Area_ID` int(11) default NULL,
  `Internal_Condition_ID` int(11) default NULL,
  `External_Condition_ID` int(11) default NULL,
  `Plot_Size_ID` int(11) default NULL,
  `Internal_Square_Footage` decimal(11,2) default NULL,
  `Internal_Square_Meters` decimal(11,2) default NULL,
  `Price_Per_Square_Foot` decimal(6,2) default NULL,
  `Price_Per_Square_Meter` decimal(6,2) default NULL,
  `Forced_Sale` char(3) NOT NULL default 'No',
  `Sheltered_Accommodation` char(3) NOT NULL default 'No',
  `Direct_From_Developer` char(3) NOT NULL default 'No',
  `Reposession` char(3) NOT NULL default 'No',
  `Requires_Updating` char(3) NOT NULL default 'No',
  `Ex_Local_Authority` char(3) NOT NULL default 'No',
  `Requires_Modernisation` char(3) NOT NULL default 'No',
  `Executors_Sale` char(3) NOT NULL default 'No',
  `Deposit_Paid_Or_Part_Exchange` char(3) NOT NULL default 'No',
  `Shared_Equity` char(3) NOT NULL default 'No',
  `Ex_Show_Home_Or_Site_Office` char(3) NOT NULL default 'No',
  `Tenanted_Or_Part_Tenanted` char(3) NOT NULL default 'No',
  `Auction_Sale` char(3) NOT NULL default 'No',
  `Listed_Building` char(3) NOT NULL default 'No',
  `Grade_ID` int(11) default NULL,
  `Conservation_Area` char(3) NOT NULL default 'No',
  `Number_Of_Garages` int(11) default NULL,
  `Resident_Parking` char(3) NOT NULL default 'No',
  `Number_Of_Parking_Spaces` int(11) default NULL,
  `Other_Factors_Affecting_Sale` text,
  `Measurement_Type` varchar(10) default NULL,
  PRIMARY KEY  (`Property_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> SELECT @SourceX := PC.X_COORD, @SourceY := PC.Y_COORD, @TargetR := 
O.Radius
-> FROM Postcodes PC
-> INNER JOIN Offices O
-> ON SUBSTRING(O.Postcode, 1, LOCATE(' ', O.Postcode) + 1 ) = 
PC.PCDSECT
-> INNER JOIN Users U
-> ON U.Office_ID = O.Office_ID
-> AND U.User_ID = 183;
+++--+
| @SourceX := PC.X_COORD | @SourceY := PC.Y_COORD | @TargetR := O.Radius |
+++--+
|   292312.9 |92463.8 |1 |
+++--+
1 row in set (0.00 sec)

mysql>
mysql> CREATE TEMPORARY TABLE tmpShortList (KEY(PCDSECT)) SELECT
-> PC.PCDSECT,
-> (@SourceX - PC.X_COORD) X_DIFF,
-> (@SourceY - PC.Y_COORD) Y_DIFF
-> FROM Postcodes PC
-> WHERE PC.X_COORD BETWEEN (@SourceX - (1609 * @TargetR)) AND (@SourceX 
+ (1609 * @TargetR))
-> AND PC.Y_COORD BETWEEN (@SourceY - (1609 * @TargetR)) AND (@SourceY + 
(1609 * @TargetR))
-> HAVING SQRT(X_DIFF*X_DIFF + Y_DIFF*Y_DIFF) <= (1609 * @TargetR);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT DATE_FORMAT(P1.Insertion_Date, "%D of %M %Y") AS "Insertion 
Date",
-> S.Name AS "Uploaded By",
-> P1.Property_Name_Or_Number AS "Name or Number",
-> P1.Address_Line_1 AS "Address Line 1",
-> P1.Address_Line_2 AS "Address Line 2",
-> P1.City AS "City", P1.County AS "County",
-> P1.Postcode AS "Postcode",
-> CONCAT("", P1.Asking_Price) AS "Asking Price",
-> CONCAT("", P1.Sale_Price) AS "Sale Price",
-> DATE_FORMAT(P1.Date_On_Market, "%D of %M %Y") AS "Date on Market",
-> DATE_FORMAT(P1.Exchange_Date, "%D of %M %Y") AS "Exchange Date",
-> T1.Tenure AS "Tenure", P1.Years_On_Lease AS "Years on Lease",
-> T2.Tax_Band AS "Tax Band",
-> A.Age AS "Age",
-> C.Category AS "Category",
-> T3.Type AS "Type",
-> P1.Number_Of_Bedrooms AS "Number of Bedrooms",
-> P1.Number_Of_Bathrooms AS "Number of Bathrooms",
-> P1.Number_Of_Receptions AS "Number of Bathrooms",
-> P1.Number_Of_Kitchens AS "Number of Kitchens",
-> D.Desirability_Of_Area_ID AS "Desirability of Area",
-> I.Internal_Condition_ID AS "

Character set problem

[Mester József]

Hy
  
  I have a table datas like that : 
  name 
  Mester József
  Job György
  Czibere Lajos
  
  If I create :
  
  select name from dolgozok where name like '%jó%' ;
  
  then all data will be shown.
  But I wolud like see datas which really contain ó character (only Mester 
József).
  
  Joe
  
  

-
Win a BlackBerry device from O2 with Yahoo!. Enter now.

mysqld got signal 11

[Гаврилов Вячеслав]

Hello developers of MySql. It is my problem:
---
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=33554432
read_buffer_size=2093056
max_used_connections=22
max_connections=100
threads_connected=8
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 
441967 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
--
This message from mysql_err.log. It up very often. At 1-2 days.How you comment 
this?

In my system FreeBSD work RADIUS and write account in database on MySQL server 
. Calls friquency is 1-8 in one second.

Primary Key Generator as auto_increment replacement

[Ady Wicaksono]

Anybody has idea about primary key generator to replace auto_increment 
as primary key?


I found this problem too (http://bugs.mysql.com/bug.php?id=16979)

since huge insert/update/select count(*) work parallel
frequently, . i got deadlock :(

Thx







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[Fwd: Primary Key Generator as auto_increment replacement]

[Ady Wicaksono]

Anybody has idea about primary key generator to replace auto_increment 
as primary key?


I found this problem too (http://bugs.mysql.com/bug.php?id=16979)

since huge insert/update/select count(*) work parallel
frequently, . i got deadlock :(

Thx









--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Creating REGEXP patterns with use of subquery

[Kim Christensen]

Is there any way to build a REGEXP pattern set by using a subquery?

I have a set of rows in table "table", with the column "value" being
target for my query. That column's content is a bracket separated list
of values, like this:

[118][Word][Integer][Stuff]...
[67][Another word][Another integer][More stuff]...

Now, to get all rows which "value string" starts with "[118]", this
does the trick:

SELECT * FROM table WHERE value REGEXP '^\\[118'

And further on, to get all rows which "value string" starts with
either [21], [42] or [999], this works fine:

SELECT * FROM table WHERE value REGEXP '^\\[(21|42|999)'

But I need to be able to do this last query without having to specify
the values by hand, but with a subquery. Imagine the following query:

SELECT id FROM items WHERE parent=5

This gives me a result set of rows which "parent" columns matches 5. I
would like to use all these results in the last REGEXP query example
above, something like this:

SELECT * FROM table WHERE value REGEXP '^\\[(SELECT id FROM items
WHERE parent=5)'

Anyone got any clues?

--
Kim Christensen
[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Character set problem

[Gleb Paharenko]

Hello.

Perhaps it is an issue of your collation:

mysql> select a from ts where a like '%ó%' collate utf8_bin ;
++
| a  |
++
| Mester József  |
++
1 row in set (0.00 sec)

mysql> select a from ts where a like binary '%ó%';
++
| a  |
++
| Mester József  |
++


mysql> select a from ts where a like '%ó%';
++
| a  |
++
| Mester József  |
| Job György |
| Czibere Lajos  |
++

See:
  http://dev.mysql.com/doc/refman/5.0/en/charset-collations.html



Mester József wrote:
> Hy
>   
>   I have a table datas like that : 
>   name 
>   Mester József
>   Job György
>   Czibere Lajos
>   
>   If I create :
>   
>   select name from dolgozok where name like '%jó%' ;
>   
>   then all data will be shown.
>   But I wolud like see datas which really contain ó character (only Mester 
> József).
>   
>   Joe
>   
>   
>   
> -
> Win a BlackBerry device from O2 with Yahoo!. Enter now.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqld got signal 11

[Gleb Paharenko]

Privet!

> key_buffer_size + (read_buffer_size +
>sort_buffer_size)*max_connections = 441967 K

Check that you have enough virtual memory for MySQL processes.


Have a look at:
  http://dev.mysql.com/doc/refman/5.0/en/freebsd.html
  http://dev.mysql.com/doc/refman/5.0/en/crashing.html


What version of MySQL are you using and in what way have you installed
it. Please report an exact version of FreeBSD.






Гаврилов Вячеслав wrote:
> Hello developers of MySql. It is my problem:
> ---
> mysqld got signal 11;
> This could be because you hit a bug. It is also possible that this binary
> or one of the libraries it was linked against is corrupt, improperly built,
> or misconfigured. This error can also be caused by malfunctioning hardware.
> We will try our best to scrape up some info that will hopefully help diagnose
> the problem, but since we have already crashed, something is definitely wrong
> and this may fail.
> 
> key_buffer_size=33554432
> read_buffer_size=2093056
> max_used_connections=22
> max_connections=100
> threads_connected=8
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 
> 441967 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
> --
> This message from mysql_err.log. It up very often. At 1-2 days.How you 
> comment this?
> 
> In my system FreeBSD work RADIUS and write account in database on MySQL 
> server . Calls friquency is 1-8 in one second.
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Creating REGEXP patterns with use of subquery

[Gleb Paharenko]

Hello.

This looks like a task for prepared statements. You can dynamically
form the string from the query using GROUP_CONCAT, assign the
created string to the variable. Then just prepare a right statement and
invoke it using that variable. See:
  http://dev.mysql.com/doc/refman/5.0/en/sqlps.html
  http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html



Kim Christensen wrote:
> Is there any way to build a REGEXP pattern set by using a subquery?
> 
> I have a set of rows in table "table", with the column "value" being
> target for my query. That column's content is a bracket separated list
> of values, like this:
> 
> [118][Word][Integer][Stuff]...
> [67][Another word][Another integer][More stuff]...
> 
> Now, to get all rows which "value string" starts with "[118]", this
> does the trick:
> 
> SELECT * FROM table WHERE value REGEXP '^\\[118'
> 
> And further on, to get all rows which "value string" starts with
> either [21], [42] or [999], this works fine:
> 
> SELECT * FROM table WHERE value REGEXP '^\\[(21|42|999)'
> 
> But I need to be able to do this last query without having to specify
> the values by hand, but with a subquery. Imagine the following query:
> 
> SELECT id FROM items WHERE parent=5
> 
> This gives me a result set of rows which "parent" columns matches 5. I
> would like to use all these results in the last REGEXP query example
> above, something like this:
> 
> SELECT * FROM table WHERE value REGEXP '^\\[(SELECT id FROM items
> WHERE parent=5)'
> 
> Anyone got any clues?
> 
> --
> Kim Christensen
> [EMAIL PROTECTED]


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL LEFT JOIN Optimization Using LIMIT CLAUSE

[SGreen]

Scott Klarenbach <[EMAIL PROTECTED]> wrote on 02/02/2006 02:01:11 
PM:

> I have a table `requirement` which is left joining to a table 
`inventory`
> based on a matching `partNumber` column.  The inventory table has 
millions
> of records, the requirement table has tens of thousands.  I'm noticing 
that
> the left join between requirement and inventory doesn't take advantage 
of a
> LIMIT clause.  So whether I select all records from requirement or limit 
it
> to 50, the LEFT JOIN operation still seems to be calculating for ALL
> requirement records against ALL inventory records.  (The query takes the
> exact same amount of time, whether I pull 50 requirement records or 
10,000).
> 
> How can I force mysql to only join the inventory table for the those 50
> records brought back by the LIMIT clause?
> 
> What I would do in a more powerful DB like SQL Server, is build a 
temporary
> table with my 50 requirement rows, and then perform the inventory join 
on
> the temp table.  But due to MySQL SPROC limitations (ie, LIMIT clauses 
must
> have integer constants, not parameters) and View limititations (ie, no
> indexing of views), I'd have to build this temporary table and the rest 
of
> query in PHP first, which is really ugly.
> 
> I'm hoping there is a nice SQL trick I can use with MySQL to restrict 
the
> join to only those records that would come back from the limit set.
> 
> Thanks,
> Scott Klarenbach

Yes, and no.  You cannot apply a LIMIT specifically to a JOIN clause 
unless you break your query into separate pieces and put limits on each of 
them.  What happens during the normal execution of a query is that after 
parsing and planning the engine begins collecting and combining the source 
data. Which records are combined and matched against which others is 
defined in the FROM clause and all of the JOIN clauses. 

The equivalent to a large virtual table (similar to saying "SELECT * FROM 
") is created in memory. The only restrictions to 
which rows of data make it into this first processing stage come from the 
ON clauses (and any WHERE clauses the optimizer _may_ choose to include) 
defined between the JOINed tables. Next comes WHERE clause processing, 
then GROUP BY processing, HAVING processing, ORDER BY processing, and 
finally LIMIT processing. 

As you can see by the flow of query execution, LIMIT clauses are really 
only useful for restricting how much data is finally sent to the user. In 
order to minimize how much processing your CPU has to do to compute a 
particular query you have several tools at your disposal: indexes, 
temporary tables, and stepwize result construction.

JOINing tables is a geometrically expensive action. The number of 
potential row matches increase by the product of the number of rows in 
each table involved in the join. If you can preselect certain target rows 
from your really large tables into smaller temporary tables and build your 
final result set from them, the query processor will only need to compute 
a small fraction of the row comparisons it would have had to perform 
compared to the number of row comparisons necessary to JOIN your original 
tables. Take this rough math as an example:

TABLE A: 1 rows
TABLE B: 1 rows

SELECT * from A INNER JOIN B ON A.id = B.A_ic;

There are potentially 1 x 1 = 1 (1.0e+08) row combinations 
to be checked. If instead of joining A to B, we create two derivative 
tables called C and D (assuming we don't change the column names)

TABLE A -> TABLE C: 5000 rows
TABLE B -> TABLE D: 1000 rows

SELECT * from C INNER JOIN D ON C.id = D.A_ic;

That means there are now 5000 x 1000 = 500 (5.0e+06) or 1/20th the 
number of comparisons to run. Computing tables C and D should be in linear 
or logarithmic time (because you should have good index coverage) so there 
will usually be a net gain in performance. This is the secret to stepwize 
result construction.

To help you to optimize your particular query, I would need to see it and 
the table definitions it is working against (SHOW CREATE TABLE works best 
for me).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Creating REGEXP patterns with use of subquery

[Michael Stassen]

Kim Christensen wrote:

Is there any way to build a REGEXP pattern set by using a subquery?

I have a set of rows in table "table", with the column "value" being
target for my query. That column's content is a bracket separated list
of values, like this:

[118][Word][Integer][Stuff]...
[67][Another word][Another integer][More stuff]...

Now, to get all rows which "value string" starts with "[118]", this
does the trick:

SELECT * FROM table WHERE value REGEXP '^\\[118'

And further on, to get all rows which "value string" starts with
either [21], [42] or [999], this works fine:

SELECT * FROM table WHERE value REGEXP '^\\[(21|42|999)'

But I need to be able to do this last query without having to specify
the values by hand, but with a subquery. Imagine the following query:

SELECT id FROM items WHERE parent=5

This gives me a result set of rows which "parent" columns matches 5. I
would like to use all these results in the last REGEXP query example
above, something like this:

SELECT * FROM table WHERE value REGEXP '^\\[(SELECT id FROM items
WHERE parent=5)'

Anyone got any clues?

--
Kim Christensen
[EMAIL PROTECTED]


Gleb has already sent a suggestion to do what you asked.  I'd like to add that 
the problem you're having is one perfect example of why this is not the best 
table design.  It is a bad idea to put multiple values, especially different 
types of values, in one column.  If you have control over this, you should 
change your table so that each value is in its own column.


Change this ==> to something like this

 value  iid   word   num  stuff
 -- ---  --- ---  
 [118][Word1][6][Something] 118  'Word1'   6  'Something'
 [67][Word2][12][Something else] 67  'Word2'  12  'Something else'

Then your query is simple:

  SELECT * FROM table WHERE iid IN (SELECT id FROM items WHERE parent=5);

Better yet (probably faster), use a join:

  SELECT * FROM table
  JOIN items ON table.iid = items.id
  WHERE items.parent=5;

A simpler query is not the only benefit.  Now that your integers are actually 
stored as integers instead of as strings, the query will run much faster, 
because integer comparisons are an order of magnitude faster than string 
comparisons.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

auto_increment

[Scott Johnson]

Hi All,

I'm trying to restore a database where the first column is  set to
auto_increment. When I apply the inserts created from the mysqldump I get
errors about the auto_increment column. Is there a may to turn it off while
I run the inserts or do I have to modify the table?

Thanks,

Scott Johnson
[EMAIL PROTECTED]
Tel.: (514) 917-4922
Fax: (514) 673-0011



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Connection Problem

[sheeri kritzer]

Casey,

in a Terminal window, type

ps -ef | grep -i mysql

and see if there is a mysqld process actually running and not a zombie
(defunct).

See if you can connect on the localhost.

See if the socket exists.

I'd bet that one of those three will find your problem.   Please let
us know if this helps.

-Sheeri

On 2/2/06, Rhodes, Casey <[EMAIL PROTECTED]> wrote:
> When testing my connection via Dreamweave MX, I get the following error 
> message:
>
>
>
> 2002 Can't connect to local MySQL server through socket 
> '/var/mysql/mysql.sock' (2)
>
>
>
> It was suggested to me that the server may not be running, though when I go 
> to my System Preferences Panel (MacOSX), I have a green light and it says 
> that it is currently running.
>
>
>
>
>
> --Casey Rhodes
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problem with excluded days in date range query.

[Paul Halliday]

I have the following query:

select count(*) as cnt, date(timestamp) as day from table where
date_sub(curdate(),interval 14 day) <= timestamp group by day;

If there are no data for a particular day, it is not included. Is
there a way to include all days even if the result is 0? I saw an
example that included another table with all dates and an inner join
but that seems a little clunky.

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Visual database design system

[Adi]

I am looking for a tool to integrate with mysql...I have tried DBDesigner
and would like to get my hands on software that is equivalent or better than
DBDesigner...any suggestions? FYI: I have had some problems with importing,
printing etc with DBDesigner...

Thanks in advance...

RE: Visual database design system

[Jimmy Guerrero]

Hello,

Might want to check out ERStudio from Embarcadero Technologies.

http://www.embarcadero.com/products/erstudio/

Jimmy Guerrero, Senior Product Manager
MySQL Inc, www.mysql.com
Houston, TX USA


-Original Message-
From: Adi [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 03, 2006 9:53 AM
To: mysql@lists.mysql.com
Subject: Visual database design system


I am looking for a tool to integrate with mysql...I have tried DBDesigner
and would like to get my hands on software that is equivalent or better than
DBDesigner...any suggestions? FYI: I have had some problems with importing,
printing etc with DBDesigner...

Thanks in advance...


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problem with excluded days in date range query.

[sheeri kritzer]

Database software is not a calendar.  The data you put in is the data
you get out.  If the day is never put in, you'll never get it out.

You need to have some data source that has all the days in it, like an
external table, if you want to be able to retrieve that data. 
Otherwise, you could do it in a UDF or something, perhaps.

-Sheeri

On 2/3/06, Paul Halliday <[EMAIL PROTECTED]> wrote:
> I have the following query:
>
> select count(*) as cnt, date(timestamp) as day from table where
> date_sub(curdate(),interval 14 day) <= timestamp group by day;
>
> If there are no data for a particular day, it is not included. Is
> there a way to include all days even if the result is 0? I saw an
> example that included another table with all dates and an inner join
> but that seems a little clunky.
>
> Thanks.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unclear about key_len in EXPLAIN output

[sheeri kritzer]

The manual is large and vast, I couldn't find the text you quoted on
the page I sent. What page did you find that text on?

As well, if the key that EXPLAIN uses is multi-part, you would know
from the length of the fields.  You have to use some deductive
reasoning, but I don't think it's really murky*shrug*

-Sheeri

On 2/2/06, James Harvard <[EMAIL PROTECTED]> wrote:
> 'That other statement' that I quoted is actually just part of the paragraph 
> from the manual that you quoted!
>
> My point was that it doesn't say what units the key length is given in or 
> explain _how_ to determine the number of parts used. However the more I think 
> about it the more I see that it must be bytes, and I have posted a comment to 
> that effect on the manual page.
>
> James
>
> At 10:58 am -0500 2/2/06, sheeri kritzer wrote:
> >according to:
> >http://dev.mysql.com/doc/refman/5.0/en/explain.html
> >
> >"The key_len column indicates the length of the key that MySQL decided
> >to use. The length is NULL if the key column says NULL. Note that the
> >value of key_len enables you to determine how many parts of a
> >multiple-part key MySQL actually uses."
> >
> >there are special exceptions to that for index_merge and range join types.
> >
> >The manual agrees; you are correct; it's the length of the key.  Where
> >did you find that other statement?  It might be the index_merge join
> >type's key_len...
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: auto_increment

[Gleb Paharenko]

Hello.

Please, could add more details of your actions. Are you inserting in the
table which already has data? Please, provide exact error message. I'm
not a telepathist, but in case of duplicate key errors a brute solution
is to perform a dump with --insert-ignore option.


Scott Johnson wrote:
> Hi All,
> 
> I'm trying to restore a database where the first column is  set to
> auto_increment. When I apply the inserts created from the mysqldump I get
> errors about the auto_increment column. Is there a may to turn it off while
> I run the inserts or do I have to modify the table?
> 
> Thanks,
> 
> Scott Johnson
> [EMAIL PROTECTED]
> Tel.: (514) 917-4922
> Fax: (514) 673-0011
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: auto_increment

[sheeri kritzer]

You can change the table definition to not have the auto_increment
column, and then ALTER TABLE MODIFY COLUMN after that.  However, if
you post your errors here, perhaps you'll get the answer that actually
solves the problem, instead of working around it to possibly leave bad
data for you to discover later on?

-Sheeri

On 2/3/06, Scott Johnson <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I'm trying to restore a database where the first column is  set to
> auto_increment. When I apply the inserts created from the mysqldump I get
> errors about the auto_increment column. Is there a may to turn it off while
> I run the inserts or do I have to modify the table?
>
> Thanks,
>
> Scott Johnson
> [EMAIL PROTECTED]
> Tel.: (514) 917-4922
> Fax: (514) 673-0011
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Last access time of a table

[Andrew Braithwaite]

Hi everyone,

Does anyone know if there is a way to get the last access time from a
mysql table through mysql commands/queries?

I don't want to go to the filesystem to get this info.

I understand that this could be tricky especially as we have query
caching turned on and serve quite a few sql requests from query cache.

Can anyone help?

Cheers,

Andrew

SQL, Query

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problem with excluded days in date range query.

[Peter Brawley]

Paul,


If there are no data for a particular day, it is not included. Is
there a way to include all days even if the result is 0? I saw an
example that included another table with all dates and an inner join
but that seems a little clunky.



In SQL, enumerating data you don't have requires some kind of join with 
data you do have. A calendar table is very likely your most efficient 
solution. It'll also facilitate handling of non-working days &c.


PB



Paul Halliday wrote:

I have the following query:

select count(*) as cnt, date(timestamp) as day from table where
date_sub(curdate(),interval 14 day) <= timestamp group by day;

If there are no data for a particular day, it is not included. Is
there a way to include all days even if the result is 0? I saw an
example that included another table with all dates and an inner join
but that seems a little clunky.

Thanks.

  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Visual database design system

[Peter Brawley]

Adi,

We use Dezign from Datanamic. Not free, not expensive either.

PB

-

Adi wrote:

  I am looking for a tool to integrate with mysql...I have tried DBDesigner
and would like to get my hands on software that is equivalent or better than
DBDesigner...any suggestions? FYI: I have had some problems with importing,
printing etc with DBDesigner...

Thanks in advance...

  
  

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006
  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Visual database design system

[Adi]

Looked good, but does not suport MySQL 5 :( - cost is not really an
issue

On 2/3/06, Peter Brawley <[EMAIL PROTECTED]> wrote:
>
> Adi,
>
> We use Dezign from Datanamic. Not free, not expensive either.
>
> PB
>
> -
>
> Adi wrote:
>
> I am looking for a tool to integrate with mysql...I have tried DBDesigner
> and would like to get my hands on software that is equivalent or better than
> DBDesigner...any suggestions? FYI: I have had some problems with importing,
> printing etc with DBDesigner...
>
> Thanks in advance...
>
>   --
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006
>
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.1/250 - Release Date: 2/3/2006
>
>
>


--
Take care...
Adam

Re: Visual database design system

[Adi]

Hey Jimmy...when I reverse engineer a DB, the software does not seem to make
the relationship connections between the tables...all the keys are listed,
but does not illustrate them on the diagram...any tips? Cant find anything
in their soft help section...

Thanks...


On 2/3/06, Jimmy Guerrero <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> Might want to check out ERStudio from Embarcadero Technologies.
>
> http://www.embarcadero.com/products/erstudio/
>
> Jimmy Guerrero, Senior Product Manager
> MySQL Inc, www.mysql.com
> Houston, TX USA
>
>
> -Original Message-
> From: Adi [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 03, 2006 9:53 AM
> To: mysql@lists.mysql.com
> Subject: Visual database design system
>
>
> I am looking for a tool to integrate with mysql...I have tried DBDesigner
> and would like to get my hands on software that is equivalent or better
> than
> DBDesigner...any suggestions? FYI: I have had some problems with
> importing,
> printing etc with DBDesigner...
>
> Thanks in advance...
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


--
Take care...
Adam

Re: Unclear about key_len in EXPLAIN output

[James Harvard]

At 11:09 am -0500 3/2/06, sheeri kritzer wrote:
>The manual is large and vast, I couldn't find the text you quoted on
>the page I sent. What page did you find that text on?

It's the latter part of the second sentence from the same paragraph that you 
quoted in your e-mail.

>As well, if the key that EXPLAIN uses is multi-part, you would know
>from the length of the fields.  You have to use some deductive
>reasoning, but I don't think it's really murky*shrug*

If you have a key combined from different integer column types and text columns 
then it's not very clear what the relationship between a multi-part key and 
key_len is. From what the manual says I initially assumed that key_len simply 
represented the number off key parts used. I don't think a manual suffers from 
things being spelt out more clearly!

James Harvard

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Speed

[سيد هادی راستگوی حقی]

Dear all,
Thanks for your replies.

The main table for me is traffic_log. I use combination of recipient_id and
mobile_retry fields to uniquely identify each row in the traffic_log and use
the same combination on status_log as my foreign key to traffic_log.
Each message is saved as a row in traffic_log and its statuses are stored in
status_log.
The make me join these tow tables on both fields to find each message's
statuses.

For performing fast query, I always first select messages from traffic_log
becuase of it's indices and then select last status for each message.
I mean that i perform 2 separate queries.

But the problem arises when i want to search on statuses, so i have to join
these large tables and then select base on my status constraint and then
sort them base on their times. these parts are time consuming and i want to
make them fast.

As i said before, you may suggest me to restructure my tables.
I will appreciate your suggestions.

Thanks for your interest to solving my problem.

On 2/2/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
> Sorry, but you gave us a "best guess" situation. Your tables do not have
> any PRIMARY KEYs defined on them so I had to guess at what made each row in
> each table unique from all other rows in that table based only on your
> sample query.
>
> What value or combination of values will allow me to uniquely identify a
> single record from each table?  Armed with that information I can rework my
> solution to accurately identify what you want to know. My suggestion is that
> you add two integer-based auto_increment columns, one to each table, and
> make them the PRIMARY KEYs and foreign keys as appropriate.
>
> For example: What makes a single row of traffic_log different from each of
> the others? Is it the `recipient_id` column or a combination of values? Same
> for the `status_log` table. What makes each row different from all others?
> How do I uniquely identify a single row in `traffic_log` that corresponds to
> any random row from `status_log`?
>
> Yours,
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> سيد هادی راستگوی حقی <[EMAIL PROTECTED]> wrote on 02/02/2006 01:14:35
> AM:
>
> > Thanks for your suggestion,
> > I forget to tell that each message in traffic_log may has at least 2
> status
> > in status_log and I use to columns "recipients_id" and "mobile_retry"
> > to uniquely find each message's statuses.
> > May be I have to change my tables structure. I don't know.
> >
> > It's really important for me to show each message with it's last status.
> So
> > I have to use group by because in other way such as
> >
> > SELECT * FROM status_log
> > ORDER BY time;
> >
> > returns all statuses in order of time regards to multiple statuses for
> any
> > message. so I think that the query may be like this
> >
> > (SELECT status, recipient_id, time, mobile_retry FROM status_log GROUP
> BY
> > recipient_id HAVING time=MAX(time)) AS sts*
> > JOIN traffic_log ON traffic_log.recipient_id=sts.recipient_id AND
> > traffic_log.mobile_retry=sts.mobile_retry
> >
> > *sts --> to find last status of each message
> >
> >
> > On 2/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > >
> > >
> > >
> > > سيد هادی راستگوی حقی <[EMAIL PROTECTED]> wrote on 02/01/2006
> 11:07:49
> > > AM:
> > >
> > > > Dear All,
> > > > I need your suggestions please.
> > > >
> > > > have to large tables with these schemas:
> > > >
> > > >  Table: traffic_log
> > > > Create Table: CREATE TABLE `traffic_log` (
> > > >   `recipient_id` int(11) NOT NULL default '0',
> > > >   `retry` smallint(4) NOT NULL default '0',
> > > >   `mobile_retry` tinyint(1) NOT NULL default '0',
> > > >   `orig` varchar(13) default NULL,
> > > >   `dest` varchar(13) default NULL,
> > > >   `message` text,
> > > >   `account_id` int(11) NOT NULL default '0',
> > > >   `service_id` int(11) NOT NULL default '0',
> > > >   `dir` enum('IN','OUT') NOT NULL default 'IN',
> > > >   `plugin` varchar(30) NOT NULL default 'UNKNOWN',
> > > >   `date_entered` datetime NOT NULL default '-00-00 00:00:00',
> > > >   `replied` tinyint(4) default '0',
> > > >   KEY `account_id` (`account_id`,`recipient_id`,`mobile_retry`),
> > > >   KEY `account_id_2` (`account_id`,`date_entered`)
> > > > ) ENGINE=MyISAM DEFAULT CHARSET=utf8
> > > >
> > > > Table: status_log
> > > > Create Table: CREATE TABLE `status_log` (
> > > >   `recipient_id` int(11) NOT NULL default '0',
> > > >   `retry` smallint(4) NOT NULL default '0',
> > > >   `mobile_retry` tinyint(1) NOT NULL default '0',
> > > >   `status` smallint(5) NOT NULL default '0',
> > > >   `time` datetime NOT NULL default '-00-00 00:00:00',
> > > >   `smsc` varchar(20) NOT NULL default '',
> > > >   `priority` tinyint(2) unsigned NOT NULL default '0',
> > > >   `ack` varchar(30) NOT NULL default '',
> > > >   KEY `recipient_id_2`
> (`recipient_id`,`mobile_retry`,`time`,`status`),
> > > >   KEY `time` (`time`,`recipient_id`,`mobile_retry`,`status`)
> > >

Re: MySQL LEFT JOIN Optimization Using LIMIT CLAUSE

[Scott Klarenbach]

Thanks a lot Shawn.  As always, your advice has been very helpful.

On 2/3/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
>
> Scott Klarenbach <[EMAIL PROTECTED]> wrote on 02/02/2006 02:01:11
> PM:
>
> > I have a table `requirement` which is left joining to a table
> `inventory`
> > based on a matching `partNumber` column.  The inventory table has
> millions
> > of records, the requirement table has tens of thousands.  I'm noticing
> that
> > the left join between requirement and inventory doesn't take advantage
> of a
> > LIMIT clause.  So whether I select all records from requirement or limit
> it
> > to 50, the LEFT JOIN operation still seems to be calculating for ALL
> > requirement records against ALL inventory records.  (The query takes the
> > exact same amount of time, whether I pull 50 requirement records or
> 10,000).
> >
> > How can I force mysql to only join the inventory table for the those 50
> > records brought back by the LIMIT clause?
> >
> > What I would do in a more powerful DB like SQL Server, is build a
> temporary
> > table with my 50 requirement rows, and then perform the inventory join
> on
> > the temp table.  But due to MySQL SPROC limitations (ie, LIMIT clauses
> must
> > have integer constants, not parameters) and View limititations (ie, no
> > indexing of views), I'd have to build this temporary table and the rest
> of
> > query in PHP first, which is really ugly.
> >
> > I'm hoping there is a nice SQL trick I can use with MySQL to restrict
> the
> > join to only those records that would come back from the limit set.
> >
> > Thanks,
> > Scott Klarenbach
>
> Yes, and no.  You cannot apply a LIMIT specifically to a JOIN clause
> unless you break your query into separate pieces and put limits on each of
> them.  What happens during the normal execution of a query is that after
> parsing and planning the engine begins collecting and combining the source
> data. Which records are combined and matched against which others is defined
> in the FROM clause and all of the JOIN clauses.
>
> The equivalent to a large virtual table (similar to saying "SELECT * FROM
> ") is created in memory. The only restrictions to which
> rows of data make it into this first processing stage come from the ON
> clauses (and any WHERE clauses the optimizer _may_ choose to include)
> defined between the JOINed tables. Next comes WHERE clause processing, then
> GROUP BY processing, HAVING processing, ORDER BY processing, and finally
> LIMIT processing.
>
> As you can see by the flow of query execution, LIMIT clauses are really
> only useful for restricting how much data is finally sent to the user. In
> order to minimize how much processing your CPU has to do to compute a
> particular query you have several tools at your disposal: indexes, temporary
> tables, and stepwize result construction.
>
> JOINing tables is a geometrically expensive action. The number of
> potential row matches increase by the product of the number of rows in each
> table involved in the join. If you can preselect certain target rows from
> your really large tables into smaller temporary tables and build your final
> result set from them, the query processor will only need to compute a small
> fraction of the row comparisons it would have had to perform compared to the
> number of row comparisons necessary to JOIN your original tables. Take this
> rough math as an example:
>
> TABLE A: 1 rows
> TABLE B: 1 rows
>
> SELECT * from A INNER JOIN B ON A.id  = B.A_ic;
>
> There are potentially 1 x 1 = 1 (1.0e+08) row combinations
> to be checked. If instead of joining A to B, we create two derivative tables
> called C and D (assuming we don't change the column names)
>
> TABLE A -> TABLE C: 5000 rows
> TABLE B -> TABLE D: 1000 rows
>
> SELECT * from C INNER JOIN D ON C.id  = D.A_ic;
>
> That means there are now 5000 x 1000 = 500 (5.0e+06) or 1/20th the
> number of comparisons to run. Computing tables C and D should be in linear
> or logarithmic time (because you should have good index coverage) so there
> will usually be a net gain in performance. This is the secret to stepwize
> result construction.
>
> To help you to optimize your particular query, I would need to see it and
> the table definitions it is working against (SHOW CREATE TABLE works best
> for me).
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>

MySQL 5.0 probleam with show table status

[Dyego Pessoal]

I have a serius probleam , my backup system see the FK's with "show 
table status like 'tablename'" , and read the Comment column...

in mysql 4.x works fine... but em 5.x the FK's has cuted

ex:
| apresentante | InnoDB |   9 | Redundant  |   44 |372 
|   1
6384 |   0 |81920 | 0 | 50 | 
2005-08-04

11:19:15 | NULL| NULL   | latin1_swedish_ci | NULL |
   | InnoDB free: 01952 kB; (`Ap_UsuarioAlteracao`) REFER 
`sqlreg3/usuario`

(`Us_I | <--- * HERE IS THE PROBLEM  **


But , the "show create table command" shows:

| apresentante |CREATE TABLE `apresentante` (
 `Ap_Id` int(4) unsigned NOT NULL auto_increment,
 `Ap_Nome` varchar(200) NOT NULL default '',
 `Ap_Endereco` varchar(200) NOT NULL default '',
 `Ap_Telefone` varchar(30) NOT NULL default '',
 `Ap_EMail` varchar(200) NOT NULL default '',
 `Ap_DataInclusao` date NOT NULL default '-00-00',
 `Ap_HoraInclusao` time NOT NULL default '00:00:00',
 `Ap_DataAlteracao` date NOT NULL default '-00-00',
 `Ap_HoraAlteracao` time NOT NULL default '00:00:00',
 `Ap_UsuarioInclusao` int(4) unsigned default NULL,
 `Ap_UsuarioAlteracao` int(4) unsigned default NULL,
 UNIQUE KEY `Ap_Id` (`Ap_Id`),
 KEY `IAp_Nome` (`Ap_Nome`),
 KEY `iAp_UsDtHoInclusao` (`Ap_DataInclusao`,`Ap_HoraInclusao`),
 KEY `iAp_UsDtHoAlteracao` (`Ap_DataAlteracao`,`Ap_HoraAlteracao`),
 KEY `iap_UsuarioInclusao` (`Ap_UsuarioInclusao`),
 KEY `iap_UsuarioAlteracao` (`Ap_UsuarioAlteracao`),
 CONSTRAINT `apresentante_ibfk_1` FOREIGN KEY (`Ap_UsuarioAlteracao`) 
REFERENCE

S `usuario` (`Us_Id`),
 CONSTRAINT `apresentante_ibfk_2` FOREIGN KEY (`Ap_UsuarioInclusao`) 
REFERENCES

`usuario` (`Us_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |




I'm using MySQL 5.0.18-PRO on Linux Debian Box with kernel 
2.6.14-2-686-smp on Xeon 2.4 machine with 1GB of RAM


Using InnoDB tables...


Tnks in advance

Innodb,MySQL,user,hlllppp


--



-
++  Dyego Souza Dantas Leal   ++   Dep. Desenvolvimento   
-
  E S C R I B A   I N F O R M A T I C A
   ***http://javacoffe.blogspot.com***
-
The only stupid question is the unasked one (somewhere in Linux's HowTo)
Linux registred user : #230601
--ICQ   : 1647350
$ look into "my eyes" Phone : +55 041 2106-1212


look: cannot open my eyes Fax   : +55 041 3296-6640 
-
Reply: [EMAIL PROTECTED] 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 5.0 probleam with show table status

[Martijn Tonies]

Hi,

> I have a serius probleam , my backup system see the FK's with "show
> table status like 'tablename'" , and read the Comment column...
> in mysql 4.x works fine... but em 5.x the FK's has cuted

This didn't work "fine" in 4.x at all. The "comments" columns
was being misused to report FKs. Even more: it was being
mis-used AND it was unreliable.

> ex:
> | apresentante | InnoDB |   9 | Redundant  |   44 |372
> |   1
> 6384 |   0 |81920 | 0 | 50 |
> 2005-08-04
> 11:19:15 | NULL| NULL   | latin1_swedish_ci | NULL |
> | InnoDB free: 01952 kB; (`Ap_UsuarioAlteracao`) REFER
> `sqlreg3/usuario`
> (`Us_I | <--- * HERE IS THE PROBLEM  **
>
>
> But , the "show create table command" shows:

--8<-- snip

>   KEY `iap_UsuarioAlteracao` (`Ap_UsuarioAlteracao`),
>   CONSTRAINT `apresentante_ibfk_1` FOREIGN KEY (`Ap_UsuarioAlteracao`)
> REFERENCE
> S `usuario` (`Us_Id`),
>   CONSTRAINT `apresentante_ibfk_2` FOREIGN KEY (`Ap_UsuarioInclusao`)
> REFERENCES
>  `usuario` (`Us_Id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>

This is is the only way to get the FK data.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Visual database design system

[Steve Edberg]

At 10:52 AM -0500 2/3/06, Adi wrote:

I am looking for a tool to integrate with mysql...I have tried DBDesigner
and would like to get my hands on software that is equivalent or better than
DBDesigner...any suggestions? FYI: I have had some problems with importing,
printing etc with DBDesigner...

Thanks in advance...



I've used Artiso Visual Case -

http://www.visualcase.com/

- some. Java-based, still a bit rough here and there, but they have a 
free 30-day trial. Academic price (what I paid) much less expensive 
than standard license. I haven't tried it with MySQL 5 yet. If I 
recall clearly, it is similar in scope to Datanamic's DeZign -


http://www.datanamic.com/

- which is (or was) Windows only.

steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Last access time of a table

[Jake Peavy]

On 2/3/06, Andrew Braithwaite <[EMAIL PROTECTED]> wrote:
>
> Hi everyone,
>
> Does anyone know if there is a way to get the last access time from a
> mysql table through mysql commands/queries?
>
> I don't want to go to the filesystem to get this info.
>
> I understand that this could be tricky especially as we have query
> caching turned on and serve quite a few sql requests from query cache.
>
> Can anyone help?
>
> Cheers,
>
> Andrew
>
> SQL, Query



Well, you didn't include your MySQL version, but if you're 5.0+ you could
do:

select update_time from information_schema.tables where
table_name='tablename'

Otherwise, you could try to use the binlog.  The filesystem sure would be
easy though...

-jp