RE: Slow query times
I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- 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: Slow query times
I couldn't say without some hard benchmarks. Just keep in mind that an index still only breaks your data up into fairly large compartments which must be scanned. It's like this: rows scanned = # rows / (log2(# of options)) At some threshold of log2(# of options) it's faster to scan the whole table. MySQL usually detects this and ignores the index, especially if another index was already used to break down the number of rows. Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 2:46 PM To: Matt Griffin Cc: [EMAIL PROTECTED] Subject: Re: Slow query times You are perfectly correct. Without the index on sex query times are lightning fast. I don't quite get you explanation why; where can I read up on how queries are done with and without an index ? Can one make a generalization that unless the number of different values in a column is less than log(number of rows), it does not make sense to use an index ? Thanks, Balazs On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote: I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- 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 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: Slow query times
Ignore that silly equation. I tried to simplify and ended up with something mathematically ridiculous. I'm sure someone can come up with a more accurate simplification. Matt -Original Message- From: Matt Griffin [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 3:46 PM To: 'Balazs Rauznitz' Cc: [EMAIL PROTECTED] Subject: RE: Slow query times I couldn't say without some hard benchmarks. Just keep in mind that an index still only breaks your data up into fairly large compartments which must be scanned. It's like this: rows scanned = # rows / (log2(# of options)) At some threshold of log2(# of options) it's faster to scan the whole table. MySQL usually detects this and ignores the index, especially if another index was already used to break down the number of rows. Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 2:46 PM To: Matt Griffin Cc: [EMAIL PROTECTED] Subject: Re: Slow query times You are perfectly correct. Without the index on sex query times are lightning fast. I don't quite get you explanation why; where can I read up on how queries are done with and without an index ? Can one make a generalization that unless the number of different values in a column is less than log(number of rows), it does not make sense to use an index ? Thanks, Balazs On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote: I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- 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 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: hierarchical records, I need some help!! ;(
I have been using adjacency lists to solve problems with hierarchical data.
The algorithm is described in a relatively database independent way here:
http://www.intelligententerprise.com/001020/celko1_1.shtml
And by the same author here: http://www.dbmsmag.com/9603d06.html
Includes a tiny bit more on nested set models.
Cheers,
Matt
-Original Message-
From: Steve Folly [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 1:59 PM
To: MySQL MySQL
Subject: Re: hierarchical records, I need some help!! ;(
On 13 Jan 2004, at 09:19, Victor Reus wrote:
Hi again,
Second: specify what you mean with all relations. Can you
share some sample data and sample output (that is: what
do you expect the query to return).
Ok my table noms is like
| id | int(11) | | PRI | [NULL] | auto_increment
| reference| varchar(255) |
| component| varchar(255) |
The relation between refefence and component are like parent - child
Here is an example
Table contains :
IDREFERENCE COMPONENT
1 A004 B001
2 B001 C003
3 B001 D003
(I have more records but only this are what i want to take as an
example)
So the relations are like a hierarchical tree:
A004
BOO1
C003
D003
And i want one sentence that takes all the relations.
The SQL statement result should be like:
REFERENCE COMPONENT
AOO4 B001
B001 COO3
B001 COO3
Coudl somebody give me a tip to do it?.
Thanks.
Victor.
--
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: How to READ/WRITE directly on MyISAM data files ?
There's no way to hide a row. However, I can think of a zany solution. Update your tables using a hash of all the data in the row. That way you don't need an id field. Fair warning: this is not a GOOD solution but it does address the problem. Matt -Original Message- From: Stéphane Bischoff [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 9:00 AM To: 'Jay Blanchard'; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? Hello, Here's an example : I have a table named Product. TABLE : PRODUCT +-+-+-+-+-+ |RowID| Name| Company| Price | Warranty | | | | | | | +-+-+-+-+-+ | 1 | PC 1000 | MyCom Inc. | 1000.00| 1 year| | 2 | PC 1000 | MyCom Inc. | 1200.00| 2 year| | 3 | PC 1000 | MyCom Inc. | 1300.00| 3 year| | 4 | PC 2000 | MyCom Inc. | 1200.00| 1 year| | 4 | PC 2000 | MyCom Inc. | 1300.00| 2 year| | 4 | PC 2000 | MyCom Inc. | 1400.00| 3 year| | 4 | PC 3000 | MyCom Inc. | 1500.00| 1 year| | 4 | PC 3000 | MyCom Inc. | 1600.00| 2 year| | 4 | PC 3000 | MyCom Inc. | 1700.00| 3 year| | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+-+ From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; But For My Client Side Applications : User logs in my Client app. User types in SELECT * FROM product. User does NOT WANT TO SEE RowID numbers. User wants to see this output : +-+-+-+-+ | Name| Company| Price | Warranty | | | | | | +-+-+-+-+ | PC 1000 | MyCom Inc. | 1000.00| 1 year| | PC 1000 | MyCom Inc. | 1200.00| 2 year| | PC 1000 | MyCom Inc. | 1300.00| 3 year| | PC 2000 | MyCom Inc. | 1200.00| 1 year| | PC 2000 | MyCom Inc. | 1300.00| 2 year| | PC 2000 | MyCom Inc. | 1400.00| 3 year| | PC 3000 | MyCom Inc. | 1500.00| 1 year| | PC 3000 | MyCom Inc. | 1600.00| 2 year| | PC 3000 | MyCom Inc. | 1700.00| 3 year| | PC AR3| SPCom Inc. | 1200.00| 2 year| | PC AR3| SPCom Inc. | 1300.00| 3 year| | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+ I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. Best Regards, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:49 To: Stéphane Bischoff; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. [/snip] Well, that pretty much misses the point then, doesn't it? Are your records not unique? There is no way that you can write an update statement that would perform the operation on the proper record? Can you show us a bit of the table? With more information we can help. -- 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: Problem with mysqlimport
If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Asking again... please help me!!!
MySQL doesn't return HTTP error codes. Your problem is not with mysql but with Dreamweaver's browser not being pointed at a running webserver. Did you set up Apache (or some other server) when you set up PHP and prove that both are functioning? Matt -Original Message- From: Lost Idols [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 11:15 AM To: [EMAIL PROTECTED] Subject: Asking again... please help me!!! I did ask this before, but haven't got any help yet. I'm trying this again... so please let me know if you know. Setting up MySQL and PHP on my Mac OS X 10.2 Just made it work... well, at least I now have a databse that I created and a table with two things inserted. I can also see them when I do a SELECT... So, since I know it's working, I started my DW MX to try to work from there, but I just get an error. I've been setting up the database with all the info and when I want to select from the list of databases I just get the following message: HTTP Error Code 404 File Not Found. Here are some possible reasons for the problem: 1) There is no testing server running on the server machine. What's wrong? Any clues here in this list? Staffan PS. I'm a newbie, so please write in newbie language ;-) _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- 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: Problem with mysqlimport
Paul, --local is a valid option for mysqlimport in 3.23.49 according to the manual. (Source: http://www.cict.fr/app/mysql/manual.html#mysqlimport) What is the entire command you are using? Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 12:06 PM To: [EMAIL PROTECTED] Subject: Re: Problem with mysqlimport I tryed this option but i got following error: mysqlimport: Error: The used command is not allowed with this MySQL version, when using table: gwarancje MySQL version is 3.23.49 What Can I do ?? Regards - Original Message - From: Matt Griffin [EMAIL PROTECTED] To: 'Pawe3 Filutowski' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 5:11 PM Subject: RE: Problem with mysqlimport If the the file is on the same machine as your shell is running, specify --local when running mysqlimport. Matt -Original Message- From: Pawe Filutowski [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Problem with mysqlimport I tryed to import from text file (columns divided by tabulators) like this: 5724KF2003CSDEUROPAPARKAN2003-12-12MarcinTamka nono . . . On PHPTriad (under Windows 2000) it works perfectly ! I use command: mysqlimport database file.txt But under Linux (RedHat) i have serious problem. System indicates problem: mysqlimport: Error: Can't get stat of '/root/mysql/gwarancje.txt' (Errcode: 13), when using table: gwarancje where gwarancje is the name of table. I tryed to set privileges by chmod 777 command or smilar but it still doesn`t work. Does anybody have any ideas ?? Regards, Paul -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext TODO items
Hi all, I've searched the development roadmaps and I can't find any of the Fulltext TO DO items (http://www.mysql.com/doc/en/Fulltext_TODO.html) on them. Can anyone point me to something which would confirm or deny plans to implement these features? I am particularly interested in proximity operators. Matt Griffin Software Developer Nerac, Inc. One Technology Drive Tolland, CT 06084 phone: 860-872-7000, ext. 328 [EMAIL PROTECTED] www.nerac.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
