Status variable select_full_join
The manual says that this variable should be as low as possible. At the moment, its value is growing slowly but steadily in my database, and I'm trying to check where the problem is. I have inserted counters into each program module, so I can tell which queries are being executed. To the best of my knowledge, I have created indexes for every field which features in a 'where' or 'join' statement, but there is obviously something not being covered. Is there any systematic way of finding out which queries are causing 'select_full_join' to grow? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Improving a query
I have a query which was written like this select p.id, p.name, p.money, s.name, agents.name from projects p, status s, agents where p.stage = 0 and p.status = s.id and p.agent = agents.id order by p.name Despite the fact that p.name is indexed, 'explain' shows in the extra column 'using temporary; using filesort'. I don't understand why. Also, I have reason to believe that this query is bumping up the 'select_full_join' count, so I rewrote it as select p.id, p.name, p.money, s.name, agents.name from projects p left join status s on s.id = p.status left join agents on agents.id = p.agent order by p.name This doesn't improve the data returned by 'explain', and it seems that 'select_full_join' increased by two (instead of one, as before). The 'status' and 'agents' tables exist in the query only to give values to the pointers stored in the 'projects' table (after all, this is a relational database with each name being stored only once). So what's the most efficient way to get at the data? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Improving a query
| |
| projects | 1 | i_matzav |1 | matzav | A
| 2 | NULL | NULL | |
| projects | 1 | i_sug |1 | sug | A
| 2 | NULL | NULL | |
| projects | 1 | i_sochen |1 | sochen | A
| 7 | NULL | NULL | |
| projects | 1 | i_adrichal |1 | adrichal| A
| 266 | NULL | NULL | |
| projects | 1 | i_customer |1 | customer| A
| 333 | NULL | NULL | |
| projects | 1 | i_managers |1 | managers| A
| 133 | NULL | NULL | |
| projects | 1 | i_name |1 | name| A
|1334 | NULL | NULL | |
| projects | 1 | i_odate|1 | openeddate | A
| 190 | NULL | NULL | |
| projects | 1 | i_cdate|1 | closeddate | A
| 190 | NULL | NULL | |
| projects | 1 | i_status |1 | status | A
| 4 | NULL | NULL | |
+--+++--+-+-
--+-+--++-+
11 rows in set (0.00 sec)
{ What does the cardinality mean? There should be three values for matzav,
three for sug and five for status. Is this zero based? }
mysql explain select p.id, p.name, p.schum, s.name, sochnim.name
- from projects p, status s, sochnim
- where p.matzav = 0
- and p.status = s.id
- and p.sochen = sochnim.id
- order by p.name;
+-+++--+-+--
+--+-+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-+++--+-+--
+--+-+
| p | ref| i_matzav,i_sochen,i_status | i_matzav | 2 | const
| 353 | Using temporary; Using filesort |
| s | ALL| PRIMARY| NULL |NULL | NULL
|5 | where used |
| sochnim | eq_ref | PRIMARY| PRIMARY | 3 |
p.sochen |1 | |
+-+++--+-+--
+--+-+
3 rows in set (0.00 sec)
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 20, 2004 3:33 PM
To:
Cc: Noamn
Subject: Re: Improving a query
* Noamn
I have a query which was written like this
select p.id, p.name, p.money, s.name, agents.name
from projects p, status s, agents
where p.stage = 0
and p.status = s.id
and p.agent = agents.id
order by p.name
Despite the fact that p.name is indexed, 'explain' shows in the
extra column
'using temporary; using filesort'. I don't understand why.
Could you show us the output of this EXPLAIN, and also SHOW CREATE TABLE on
projects, status and actors?
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Improving a query
The query itself executes quite fast, but as it's one of the most frequently executed queries in the entire program, I thought it important to make it as fast as possible. -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 11:40 PM To: mysql Cc: Noamn Subject: Re: Improving a query * Noamn [...] Please reply to the list. :) Adding the composite index doesn't make any difference as far as I can see. Isn't there a way of forcing a specific index to be used? Yes. From mysql version 4.0.9 you can write FORCE INDEX, for earlier versions you can try USE INDEX. URL: http://www.mysql.com/doc/en/SELECT.html Does all the above have anything to do with the 'select_full_join' variable? Well... the 'select_full_join' variable should increase every time you do a join without keys... but check below. Presumably this gets increased everytime a table has the type ALL, and that's what I want to avoid. Note that the ALL probably is because there are very few rows in the status table. URL: http://www.mysql.com/doc/en/How_to_avoid_table_scan.html What execution times are you looking at? Is it very slow? -- Roger -- 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]
Improving queries - small indexes and tables
Judging by some of the comments posed on this list, I wonder whether the following statements are true: 1. There is no point having an index on a field if that field can only have a few values 2. A table should have at least ten entries, in order to prevent all the table being scanned to find a match in a query. Does this mean that one should pad out a table (ie add spurious values with an 'ignore' flag) in order to force mySQL to use an index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem displaying 'count'
I don't know whether this is a problem with mySQL or Zeos (freeware Delphi components), but I'll ask here: When I use 'count' in a one table query, Zeos defines the resulting field as TIntegerField. When I use 'count' in a two table query, the resulting field is TLargeIntField, which causes problems in my application. It has been suggested to me to cast the result as an integer, but unfortunately 'cast' isn't present in the version of mySQL which I'm using (3.23.51 on Linux). Does anyone have any ideas how to ensure that the result is an integer? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A little help with this select?
Rory wrote: The query is behaving exactly as it should. Your query asks it to return only those workers that are listed in the webprojectassign table. Seeing as there are no entries in the webprojectassign table, no workers match and hence no results are produced. No'am adds: I think that Tyler wanted the workers who aren't listed in the webprojectassign table - his query contains WHERE worker.workerid!=webprojectassign.workerid Is != a different way of writing ? If he wants the unassigned workers, then he needs what I've seen referred to as a 'theta join', and I too would be interested to see how to do this in mySQL. When I've needed such a query, to find 'childless' records, I've always done it with a looped query in the client program. -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:19 AM To: Tyler Longren; [EMAIL PROTECTED] Subject: Re: A little help with this select? On 6 Jan 2004 at 1:00, Tyler Longren wrote: Hi, I'm baffled. Been lookin at this for the last hour now. SELECT worker.fname,worker.lname,worker.workerid FROM worker,webprojectassign WHERE worker.workerid!=webprojectassign.workerid ORDER BY worker.lname ASC; That's the SQL in question. There's currently nothing in the webprojectassign table. So shouldn't this SQL just return the fname,lname,workerid from the workers table? Could someone just explain to me why this doesn't work the way I expected it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL in Hebrew/my.cnf
I asked about a week ago how to get mySQL to index correctly in Hebrew, and the best answer that I received was to define a my.cnf file as follows [mysqld] set-variable = default-character-set = hebrew I created the file /etc/my.cnf using the root account, stopped the mysql daemon then restarted. The daemon failed immediately. I tried this a few more times, then reluctantly came to the conclusion that there is something wrong with the /etc/my.cnf file, so I deleted it and successfully started the daemon. Is there something special which I need to define regarding the file's permissions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL in Hebrew
I have run the 'show variables' query on my database and see that the default character set is latin-1, and 'character_sets' includes 'hebrew'. So I tried the following command alter database presale default character set hebrew; only to be awarded with a 'you have an error in your SQL syntax' message. 'Alter table' gives the same message. I even copy and pasted the example in the online documentation, which gave me the same answer. I am using version 3.23.51 on linux. -Original Message- From: Noamn [mailto:[EMAIL PROTECTED] Sent: Friday, December 26, 2003 8:22 AM To: [EMAIL PROTECTED] Subject: mySQL in Hebrew Does anyone have any experience of working with mySQL in Hebrew? The front end to my database is written in Delphi/Windows; it transmits Hebrew characters and displays Hebrew characters. The backend is in mySQL/Linux, and what was Hebrew appears in English characters. The only problem which I have with this is that almost every table has a 'name' index, and the names aren't sorted in the correct order. I had a quick glance at the documentation regarding internationalisation and didn't completely understand it, so I am asking for help. Would it be a good idea to build a translation table, ie 'aleph' = 'a' and comes first in the sorting order? What about numbers and English names? -- 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]
mySQL in Hebrew
Does anyone have any experience of working with mySQL in Hebrew? The front end to my database is written in Delphi/Windows; it transmits Hebrew characters and displays Hebrew characters. The backend is in mySQL/Linux, and what was Hebrew appears in English characters. The only problem which I have with this is that almost every table has a 'name' index, and the names aren't sorted in the correct order. I had a quick glance at the documentation regarding internationalisation and didn't completely understand it, so I am asking for help. Would it be a good idea to build a translation table, ie 'aleph' = 'a' and comes first in the sorting order? What about numbers and English names? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Comparing date fields (Delphi programmers take note)
Thanks for all those who helped me with this a few days ago (I've only now
had the chance to get back to programming).
As someone correctly surmised, I am using Delphi and connecting to mySQL via
a TQuery (actually a TZQuery, via Zeos components). What I have discovered
is that
1. mySQL doesn't like queries with date parameters (one must pass the date
as a literal)
2. the date format has to be changed to that of mySQL.
3. if one uses a table alias (p in my case), one is not allowed to use the
full table name - projects.closeddate causes an error, but p.closeddate is
ok.
So I wrote a small function (called date2sql) which turns 18/12/2003 into
2003-12-18 (complete with double quotation marks), and then in my code I
write
with query1 do
begin
close;
sql[3]:= 'where p.closeddate between ' + date2sql (edFromDate.text) + ' and
' + date2sql (edTillDate.text);
open
end;
This works as it should.
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 4:47 PM
To: [EMAIL PROTECTED]
Subject: Re: Re[2]: Comparing date fields
mysql select projects.name from projects
JB - where projects.closeddate between :fd and :td;
JB ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at
JB line
JB 2
JB What are those colons?
He probably uses Delphi or C++Builder to connect to mysql via TQuery
component. TQuery has a property SQL where sql statement is stored.
The colon ':' means that fd is a parameter whose value should be
inserted into TQuery.SQL statement just before executing the query.
The solution is to substitute :fd with a real value directly by accessing
TQuery.SQL
property.
Query.SQL.Clear;
Query.SQL.Add('select ...');
Query.SQL.Add('where projects.closeddate between 01/01/01 and 01/01/02');
Query.Open;
I very much doubt this works:
01 divided by 01 :-)
I guess you wanted to write:
'01/01/01'
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL
Server.
Upscene Productions
http://www.upscene.com
--
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]
'Sum' syntax
I'm using v3.23.51 on Linux. Can anyone explain why a simple 'sum' select won't work?? Here is the example: mysql select sum (id), pet from visits group by pet; ERROR 1064: You have an error in your SQL syntax near '(id), pet from visits group by pet' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Comparing date fields
I'm in the process of converting an application from Paradox to mySQL which is why I have these syntax questions. mySQL doesn't like a query with this syntax: select p.name, from projects p, ... where p.closeddate = :fd and p.closeddate = :td ... where fd (from date) and td (till date) are parameters. Can one use greater than/less than operators, or is there some other way of filtering only those records with closeddates in the range that I specify? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Comparing date fields
I started with between, but when this didn't work, I switched to the comparison operators. mysql select projects.name from projects - where projects.closeddate between :fd and :td; ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at line 2 mysql -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 4:04 PM To: Noamn; [EMAIL PROTECTED] Subject: RE: Comparing date fields [snip] I'm in the process of converting an application from Paradox to mySQL which is why I have these syntax questions. mySQL doesn't like a query with this syntax: select p.name, from projects p, ... where p.closeddate = :fd and p.closeddate = :td ... where fd (from date) and td (till date) are parameters. Can one use greater than/less than operators, or is there some other way of filtering only those records with closeddates in the range that I specify? [/snip] You can use BETWEEN (which is inclusive of the dates specified) select p.name, from projects p, ... where p.closeddate BETWEEN date AND date -- 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: Re[2]: Comparing date fields
I assume that I will have to write 01/01/02
Thanks for the help. Let's hope that there aren't any more questions of this
ilk and I can continue to port my application.
No'am
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 4:47 PM
To: [EMAIL PROTECTED]
Subject: Re: Re[2]: Comparing date fields
mysql select projects.name from projects
JB - where projects.closeddate between :fd and :td;
JB ERROR 1064: You have an error in your SQL syntax near ':fd and :td' at
JB line
JB 2
JB What are those colons?
He probably uses Delphi or C++Builder to connect to mysql via TQuery
component. TQuery has a property SQL where sql statement is stored.
The colon ':' means that fd is a parameter whose value should be
inserted into TQuery.SQL statement just before executing the query.
The solution is to substitute :fd with a real value directly by accessing
TQuery.SQL
property.
Query.SQL.Clear;
Query.SQL.Add('select ...');
Query.SQL.Add('where projects.closeddate between 01/01/01 and 01/01/02');
Query.Open;
I very much doubt this works:
01 divided by 01 :-)
I guess you wanted to write:
'01/01/01'
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL
Server.
Upscene Productions
http://www.upscene.com
--
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]
Linux access question
About a year ago, I set up mySQL on a computer running linux (probably RH7.1). At the time I thought that I would investigate how I could use this server in my business, but then I had more pressing issues and so neglected the subject. Now that I have some spare times and correct tools (Delphi 7), I want to renew my acquaintance with mySQL. I am trying to access the server from two computers running Win98. Both of them can connect to the linux machine via telnet and run programs such as the command line 'mysql', but only one of them allows me to connect via windows programs (I am using a program imaginatively called 'mysql_test.exe'). Of course, the machine which doesn't connect is the machine on which I am doing the program development. As far as I can figure out, there is some table (either in mySQL or in linux) which allows '[EMAIL PROTECTED]' to connect, but does not allow '[EMAIL PROTECTED]' to connect. What I am asking you, dear readers, is to help me find where I can add a definition to allow the second machine (and eventually others) to connect. I seem to be using version 3.22 TIA, No'am -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux access question
Thanks to all who responded to my query. Had I read a bit more of the mySQL manual, I would have seen exactly what I needed (section 4.4.5). The only problem was that the mySQL monitor has to be run from the same computer on which mySQL is running; yesterday I disconnected the screen from my linux server and stuck the computer on top of a cupboard. So now I had to shut it down, move it to my workbench, connect a screen, start the computer, type in the 'grant' commands, shut down the computer, disconnect the screen, move the server back to the cupboard, and start it up again. Now my simple Delphi program works! Hurray! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
