suggestion for more efficient inner join algorithm

2003-04-02 Thread Tim Samshuijzen 


Hello,

We run a large on-line books database which is searched a lot. We are using
MySQL but we are seriously running into optimisation problems because the
searches are becoming slower and slower as the database grows.
Simplifying our situation, visitors need to search for author and/or title and
whether the book is available. We have created our own form of 
full-text-search
by extracting all words from the author and title fields and inserting them 
in a
keywords table. To save space, we reference the keywords as numbers, as
each unique word has an entry in a separate dictionary table.

Oversimplified, the database looks like this:

CREATE TABLE books (
   BookNumber int(11) NOT NULL auto_increment,
   Author varchar(60) NOT NULL,
   Titel text NOT NULL,
   Available tinyint(4) NOT NULL,
   PRIMARY KEY (BookNumber),
   KEY FirstBook (FirstEdition, BookNumber)
);
CREATE TABLE dictionary (
   WordNumber int(11) NOT NULL auto_increment,
   Keyword varchar(50) NOT NULL,
   PRIMARY KEY (WordNumber),
   KEY Keyword (Keyword)
);
CREATE TABLE keywords (
   WordNumber int(11) NOT NULL,
   BookNumber int(11) NOT NULL,
   KEY WordBook (WordNumber, BookNumber),
   KEY BookNumber (BookNumber)
);
When someone does a search, first our PHP script locates the WordNumber
values of the keywords entered for searching. The eventual main query is 
basically
something like:

select
  b.BookID
from
  books as b, keywords as k
where
  k.BookID = b.BookID AND
  b.Available = 1 AND
  k.WordNumber = 1234
LIMIT 0,50;
Basically, this query does the following:
  - select the books which are available
  - select the keyword records where the WordNumber field equals 1234
  - return only the BookID values which appear in both these result sets
The way MySQL seems to do the query is:
 - find the smallest result set of the two sub-selects
 - for each record in the smallest result set return the BookID values that
   appear in the other result.
This method is fine if one of the sub-selects is a small result. But in our 
case
all the sub-selects are often all very large (100).
The above query takes about 5 seconds on our machine, which is much too slow.

Mathematically speaking the above method does not provide the fastest 
algorithm.

Because of the indexing, each sub-select is (can be) sorted by BookID. This 
allows
for the following very fast algorithm:

1. set a reading position to the beginning of the first sub-result
2. set a reading position to the beginning of the second sub-result
3. if an EOF is reached then exit
4. read the BookID value of the current reading position in the first 
sub-result
5. read the BookID value of the current reading position in the second 
sub-result
6. if each BookID is equal, then store the BookID value in the final result 
set,
increment both reading positions, go to 3.
7. see which BookID is the highest value.
4. within the sub-select that holds the lowest value, move the reading 
position to
the next value that is at least greater than the other current BookID 
value.
5. go to 3

The above algorithm is very fast because the sub-results are sorted by BookID.
One can imagine the algorithm to be very similar for selects which perform more
than two sub-selects.
I have tested this algorithm from a PHP script. The script terminates after 
finding
50 results (similar to LIMIT 0,50). The script finishes after executing 
about 5000
queries, all of which are performed very fast.
The eventual search time is about 5 seconds. Therefore, this takes the same 
amount
of time as the single query which we use now
I can imagine that if this fast algorithm is built into the source code of 
MySQL then
these searches become incredibly fast. Especially because each of the 5000 
queries
are single index reads.

I have heard more complaints from other MySQL users/developers who use
similar types of queries. Can anyone tell me why this algorithm is not handled
in MySQL? Is the development team planning to use such a search method?
If not, then I will build this search algorithm into MySQL myself. Is the 
MySQL
development team interested in such a search algorithm?

Finally, we find it a major problem that the SORT BY ... DESC is not optimised.
It almost always does a file-sort. We want to use this because we want to show
the most recently added records first. If this is also not on the immediate 
to-do list
of the MySQL development team, then I may aswell build this into the source 
code
myself too.

I look forward to any reactions

Best regards,

Tim Samshuijzen
Software  database engineer
mailto:[EMAIL PROTECTED]










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

question on indexes

2001-02-07 Thread Tim Samshuijzen 



Hello,

I have two tables:

CREATE TABLE categories (
   CategoryNumber int(11) NOT NULL,
   ItemNumber int(11) NOT NULL,
   KEY CategoryNumber (CategoryNumber),
   KEY ItemNumber (ItemNumber)
);

CREATE TABLE items (
   ItemNumber int(11) NOT NULL auto_increment,
   Description varchar(100) NOT NULL,
   Price bigint(20) unsigned NOT NULL,
   KEY Price (Price)
);

Each item can have multiple categories.
Each table contains more than 1 million records.

This kind of query gets executed a lot:

  SELECT I.* FROM 
items AS I, 
categories AS C
  WHERE 
 C.CategoryNumber = 123 AND 
 C.ItemNumber = I.ItemNumber AND 
 I.Price = 1000
  LIMIT 0,51;

I would like to improve the performance of this query.

My question is: 
  Would a double index on both fields in the categories table
  increase performance?
  In other words, would this structure be better:

CREATE TABLE categories (
   CategoryNumber int(11) NOT NULL,
   ItemNumber int(11) NOT NULL,
   KEY CategoryItem (CategoryNumber,ItemNumber)
);


Thanks!

Tim






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

amazingly slow

2001-02-06 Thread Tim Samshuijzen 



Hello, I don't understand why my queries are so incredibly slow. 
We have MySQL on a 800MHz Linux machine with 513Mb.
Most queries look like this:

SELECT B.* FROM maintable AS M, wordindex AS YL1, 
   wordindex AS YL2, wordindex AS YL3
WHERE 
  YL1.Word = 'billy' AND 
  YL1.RecordNumber = M.RecordNumber AND
  YL2.Word = 'bob' AND
  YL2.RecordNumber = M.RecordNumber AND
  YL3.Word = 'john' AND
  YL3.RecordNumber = M.RecordNumber AND
  M.Price = 1000
LIMIT 0,50;

wordindex is a table that contains all words present in maintable.
For each word there is a link to maintable through RecordNumber.

This query searches for all records in maintable that contain the
three words and where it's price is more than 1000.

This query takes more than 20 seconds!
I hear from others that this query should be returned in a flash!

maintable contains about 900,000 records. 
wordindex contains about 21,000,000 records

All columns are indexed.

Here are my parameters:

key_buffer=256M 
table_cache=256 
sort_buffer=1M 
record_buffer=2M 
join_buffer=4M 
max_sort_length=30 
max_connections=300

I am really desperate. I've been trying everything.
I've tried the OPTIMIZE TABLE commands, but this also doesn't help.

Anyone out there who wants to save me and our company?

Thanks a lot!

Tim








-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: amazingly slow

2001-02-06 Thread Tim Samshuijzen 



Dear Dave,

Thanks for your reply.

(The table actually works with word numbers, as the words
are present in a hash table. I explained it the way I did
because functionally it is the same. So the actual search 
is for WordNumber values instead of Word values.)

I did the EXPLAIN as you suggested.

Here is the output:

mysql EXPLAIN SELECT B.* FROM maintable AS B ,
- wordindex AS YL1 ,
- wordindex AS YL2 ,
- wordindex AS YL3 WHERE
- YL1.WordNumber = 123 AND
- YL1.RecordNumber = B.RecordNumber AND
- YL2.WordNumber = 345 AND
- YL2.RecordNumber = B.RecordNumber AND
- YL3.WordNumber = 678 AND
- YL3.RecordNumber = B.RecordNumber AND
- B.Price = 1000
- LIMIT 0,51;
+-+--+---++---++
+--+
|table|type  |possible_keys  |key |key_len|ref
|rows|Extra |
+-+--+---++---++
+--+
|YL3  |ref   |WordNumber,RecordNumber|WordNumber  | 4 |???
|  1 |  |
|B|eq_ref|PRIMARY|PRIMARY | 4
|YL3.RecordNumber|  1 |where used|
|YL2  |ref   |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber
| 23 |where used|
|YL1  |ref   |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber
| 23 |where used|
+-+--+---++---++
+--+
4 rows in set (0.01 sec)


This still does not tell me why the query is amazingly slow.

Tim


At 10:52 AM 6-2-2001 +, you wrote:
Tim Samshuijzen [EMAIL PROTECTED] writes:

 Anyone out there who wants to save me and our company?

You missed the important first step: EXPLAIN the query.

-- 
Dave Hodgkinson, http://www.hodgkinson.org
Editor-in-chief, The Highway Star   http://www.deep-purple.com
  Apache, mod_perl, MySQL, Sybase hired gun for, well, hire
  -

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: amazingly slow

2001-02-06 Thread Tim Samshuijzen 



Dear Dave,

Here is the output from vmstat 5:


vmstat 5
   procs  memoryswap  io system
cpu
 r  b  w   swpd   free   buff  cache  si  sobibo   incs  us  sy
 id
 0  0  0   3936   2980  10568 218856   8   411 33 9   0   3
 10
 0  0  0   3936   3096  10580 218728   0   0 0 1  11130   0   1
 99
 0  0  0   3936   3092  10584 218728   0   0 0 1  10926   1   1
 98
 2  0  0   3936   1676  10564 220012   0   0   287 1  212   137   1   2
 97
 2  0  0   3936   2628  10480 219084   0   0   646 2  235   130   4   3
 93
 1  0  0   3936   1652  10488 219428  22   0   727 5  456   522   2   2
 96
 0  1  0   3936   1708  10480 218776   0   0  1042 4  438   433   0   2
 97
 1  0  0   3936   2724  10484 218216  28   0  1028 0  488   557   0   3
 97
 1  0  0   3936   3080  10484 217860   0   0   308 2  297   380  34  16
 50
 1  0  0   3936   3072  10484 217860   0   0 0 0  10310  67  33
  0
 0  0  0   3936   4064  10500 218392   0   0   565 1  411   558  10   5
 84


Does this make any sense to you?

Tim


At 12:17 PM 6-2-2001 +, you wrote:
Tim Samshuijzen [EMAIL PROTECTED] writes:

 This still does not tell me why the query is amazingly slow.

What else is happening on the system? what does, say, a vmstat 5 give?


-- 
Dave Hodgkinson, http://www.hodgkinson.org
Editor-in-chief, The Highway Star   http://www.deep-purple.com
  Apache, mod_perl, MySQL, Sybase hired gun for, well, hire
  -

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: amazingly slow

2001-02-06 Thread Tim Samshuijzen 



Ren,

Ok, here is a real representation of the tables
involved:

mysql EXPLAIN maintable;
+---+-+--+-++---
-+
| Field | Type| Null | Key | Default| Extra
 |
+---+-+--+-++---
-+
| RecordNumber  | int(11) |  | PRI | 0  |
auto_increment |
| Author| varchar(60) |  | MUL ||
 |
| Titel | varchar(175)|  | MUL ||
 |
| Description   | varchar(100)|  | ||
 |
| DescripRest   | text|  | | NULL   |
 |
| Price | bigint(20) unsigned |  | MUL | 0  |
 |
+---+-+--+-++---
-+
20 rows in set (0.00 sec)

mysql EXPLAIN hashindex;
++--+--+-+-++
| Field  | Type | Null | Key | Default | Extra  |
++--+--+-+-++
| WordNumber | int(11)  |  | PRI | 0   | auto_increment |
| xWord  | char(10) |  | MUL | ||
++--+--+-+-++
2 rows in set (0.00 sec)

mysql EXPLAIN wordindex;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| WordNumber | int(11) |  | MUL | 0   |   |
| BookNumber | int(11) |  | MUL | 0   |   |
++-+--+-+-+---+
2 rows in set (0.00 sec)


So you see, the fields that are used in the query are indexed.
And the queries still take more than 10 seconds each!

Tim


At 03:35 PM 6-2-2001 +0100, you wrote:
Tim,

Hmmm... so you are suggesting the format of the query might be the cause of
this slow response. I find it hard to believe, unless you have a very big
result set i believe the query should complete within short time.

can you send the output of DESCRIBE db.tablename of all used tables?

regards,

rene



- Original Message -
From: "Tim Samshuijzen" [EMAIL PROTECTED]
To: "Ren Tegel" [EMAIL PROTECTED]
Sent: Tuesday, February 06, 2001 3:07 PM
Subject: Re: amazingly slow



Yep, all requested fields are indexed.

At 02:25 PM 6-2-2001 +0100, you wrote:
Tim,
Just kidding about the 513 Mb

you put an index on wordindex.word as well ? (it's not in the table
description but you use it in your query...Not indexing this field means
mysql searches whole table for values.. Then your P800 has a reasonable
performance on such a big table :)) )

regards,

rene

- Original Message -
From: "Tim Samshuijzen" [EMAIL PROTECTED]
To: "Ren Tegel" [EMAIL PROTECTED]
Sent: Tuesday, February 06, 2001 1:58 PM
Subject: Re: amazingly slow




Dear Ren,

Thanks for your reply.

Oops, the 513 was a typing mistake.

And yes, all the requested fields are indexed.


CREATE TABLE wordindex (
   WordNumber int(11) NOT NULL,
   RecordNumber int(11) NOT NULL,
   KEY WordNumber (WordNumber),
   KEY RecordNumber (RecordNumber)
);

CREATE TABLE books (
   RecordNumber int(11) NOT NULL auto_increment,
   Field1 varchar(60) NOT NULL,
   Field2 varchar(60) NOT NULL,
   Field3 varchar(60) NOT NULL,
   Field4 varchar(60) NOT NULL,
   Field5 varchar(60) NOT NULL,
   Price bigint(20) unsigned NOT NULL,
   PRIMARY KEY (RecordNumber),
   KEY Price (Price)
);

Any suggestions are very welcome.

Tim

At 01:51 PM 6-2-2001 +0100, you wrote:
Tim,

1. i'd remove 1 Mb from your 513 Mb machine... maybe it's an very old edo
simm or something.
2. you put an index on all requested fields (maintable.recordnumber and
wordindex.word) ? I bet not.


- Original Message -
From: "Tim Samshuijzen" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, February 06, 2001 12:04 PM
Subject: amazingly slow




 Hello, I don't understand why my queries are so incredibly slow.
 We have MySQL on a 800MHz Linux machine with 513Mb.
 Most queries look like this:

 SELECT B.* FROM maintable AS M, wordindex AS YL1,
wordindex AS YL2, wordindex AS YL3
 WHERE
   YL1.Word = 'billy' AND
   YL1.RecordNumber = M.RecordNumber AND
   YL2.Word = 'bob' AND
   YL2.RecordNumber = M.RecordNumber AND
   YL3.Word = 'john' AND
   YL3.RecordNumber = M.RecordNumber AND
   M.Price = 1000
 LIMIT 0,50;

 wordindex is a table that contains all words present in maintable.
 For each word there is a link to maintable through RecordNumber.

 This query searches for all records in maintable that contain the
 three words and where it's price is more than 1000.

 This query takes more than 20 seconds!
 I hear from others that this query should be returned in a flash!

 maintable contains about 900,000 records.
 wordindex contains about 21,000,000 records

RE: amazingly slow

2001-02-06 Thread Tim Samshuijzen 



Dear Dan,

Thanks for your reply.
I've already done OPTIMIZE for all tables.
I suppose that's the same as rebuilding the indices.

Tim

At 01:26 AM 7-2-2001 -0800, you wrote:
i've heard it said that every so often (eg every month) you need to rebuild
indexes on tables, as over time they lose their performance enhancement.
could that be true?

dan


-Original Message-
From: Tim Samshuijzen [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 6 February 2001 04:50
To: [EMAIL PROTECTED]
Subject: Re: amazingly slow




Dear Dave,

Thanks for your reply.

(The table actually works with word numbers, as the words
are present in a hash table. I explained it the way I did
because functionally it is the same. So the actual search
is for WordNumber values instead of Word values.)

I did the EXPLAIN as you suggested.

Here is the output:

mysql EXPLAIN SELECT B.* FROM maintable AS B ,
- wordindex AS YL1 ,
- wordindex AS YL2 ,
- wordindex AS YL3 WHERE
- YL1.WordNumber = 123 AND
- YL1.RecordNumber = B.RecordNumber AND
- YL2.WordNumber = 345 AND
- YL2.RecordNumber = B.RecordNumber AND
- YL3.WordNumber = 678 AND
- YL3.RecordNumber = B.RecordNumber AND
- B.Price = 1000
- LIMIT 0,51;
+-+--+---++---++
+--+
|table|type  |possible_keys  |key |key_len|ref
|rows|Extra |
+-+--+---++---++
+--+
|YL3  |ref   |WordNumber,RecordNumber|WordNumber  | 4 |???
|  1 |  |
|B|eq_ref|PRIMARY|PRIMARY | 4
|YL3.RecordNumber|  1 |where used|
|YL2  |ref   |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber
| 23 |where used|
|YL1  |ref   |WordNumber,RecordNumber|RecordNumber| 4 |B.RecordNumber
| 23 |where used|
+-+--+---++---++
+--+
4 rows in set (0.01 sec)


This still does not tell me why the query is amazingly slow.

Tim


At 10:52 AM 6-2-2001 +, you wrote:
Tim Samshuijzen [EMAIL PROTECTED] writes:

 Anyone out there who wants to save me and our company?

You missed the important first step: EXPLAIN the query.

--
Dave Hodgkinson, http://www.hodgkinson.org
Editor-in-chief, The Highway Star   http://www.deep-purple.com
  Apache, mod_perl, MySQL, Sybase hired gun for, well, hire
  -

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: PHP does not work after upgrading MySQL to 3.23.32

2001-02-03 Thread Tim Samshuijzen 



Dear Fbio Ottolini,

Thanks for your reply.

I'm using Linux 2.2.16-22.
The exact error that appears is:

/usr/local/etc/httpd/httpd: error in loading shared libraries:
libmysqlclient.so.6: cannot open shared object file: No such file or directory

In MySQL version 3.22.32 there was a file ./lib/mysql/libmysqlclient.so.6

I suppose in MySQL version 3.23.32 this file corresponds to
./lib/libmysqlclient.a

I thought it would be smart to alias link ./lib/mysql/libmysqlclient.so.6
to ./lib/libmysqlclient.a.
But when I restart httpd, the following error appears

/usr/local/etc/httpd/httpd: error in loading shared libraries:
libmysqlclient.so.6: invalid ELF header

I hope you can help me with this information.

Thanks,

Tim Samshuijzen



At 22:21 3-2-2001 -0200, you wrote:
I suppose your operational system is Windows... Or is it Linux? Regarding
Windows at least, and I believe Linux also, MySQL has got nothing to do with
your problem. If PHP is not working on your system, probabily this is
something related to your web server. Are you sure the correct MIME types
are being used? Please give as much information as you can. Otherwise it's
very difficult to come to a conclusion.

BR,

Fbio Ottolini

P.S.: Consider upgrading to PHP4.04Pl1.

- Original Message -
From: "Tim Samshuijzen" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 03, 2001 10:02 PM
Subject: PHP does not work after upgrading MySQL to 3.23.32




 Hi,

 I have just upgraded from MySQL 3.22.32 to 3.23.32. Now PHP3 does
 not work. I've tried restarting httpd, but an error appears "file
 not found", and httpd fails to start up. HELP!

 What am I doing wrong?

 Thanks,

 Tim Samshuijzen




 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: PHP does not work after upgrading MySQL to 3.23.32

2001-02-03 Thread Tim Samshuijzen 



Hey Rop,

wist je wel dat ik het was (Tim van Rockingstone).
wat toevallig dat je zat mee te kijken. (beetje laat of niet?)
bedankt voor de tip.

Tim Samshuijzen.

(Sorry for the Dutch everyone)


At 02:49 4-2-2001 +0100, you wrote:
On Sat, Feb 03, 2001 at 11:13:14PM -0200, Fbio Ottolini wrote:
 Sorry but I'm not a Linux specialist... I think WE are going to need the
 help of others. :)
 Anyway... Now you gave enough details to get help.

Tim,

I suggest you look for a new file called libmysqlclient.so (maybe in
another location) and symlink libmysqlclient.so.6 against it. After that
run 'ldconfig' and try to restart apache.

Greetings,

-
Rop Slijkerman


 
 BR,
 
 Fbio Ottolini
 
 - Original Message -
 From: "Tim Samshuijzen" [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Saturday, February 03, 2001 10:43 PM
 Subject: Re: PHP does not work after upgrading MySQL to 3.23.32
 
 
 
 
  Dear Fbio Ottolini,
 
  Thanks for your reply.
 
  I'm using Linux 2.2.16-22.
  The exact error that appears is:
 
  /usr/local/etc/httpd/httpd: error in loading shared libraries:
  libmysqlclient.so.6: cannot open shared object file: No such file or
 directory
 
  In MySQL version 3.22.32 there was a file ./lib/mysql/libmysqlclient.so.6
 
  I suppose in MySQL version 3.23.32 this file corresponds to
  ./lib/libmysqlclient.a
 
  I thought it would be smart to alias link ./lib/mysql/libmysqlclient.so.6
  to ./lib/libmysqlclient.a.
  But when I restart httpd, the following error appears
 
  /usr/local/etc/httpd/httpd: error in loading shared libraries:
  libmysqlclient.so.6: invalid ELF header
 
  I hope you can help me with this information.
 
  Thanks,
 
  Tim Samshuijzen
 
 
 
  At 22:21 3-2-2001 -0200, you wrote:

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: PHP does not work after upgrading MySQL to 3.23.32

2001-02-03 Thread Tim Samshuijzen 



I have no experience with recompiling libraries (I know this must 
sound dumb). I am a bit desperate, so I tried some other things.
So I solved it by copying ./lib/mysql/libmysqlclient.so.6
from version 3.22.32 to the same path in version 3.23.32.
I then restarted httpd.
I don't know how dangerous this is, but it works!
I will keep it this way untill someone here can recompile the libraries.

Thanks!

Tim Samshuijzen

At 21:02 3-2-2001 -0500, you wrote:
The mysql version compiled into your web server uses shared libraries and
your current version (upgrade) apparently is a statically linked version.
Recompile your web server/php/mysql executable with consistent use of
libraries i.e. either static or shared.

I hope this helps...
Pat...

- Original Message -
From: "Tim Samshuijzen" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 03, 2001 7:43 PM
Subject: Re: PHP does not work after upgrading MySQL to 3.23.32




 Dear Fbio Ottolini,

 Thanks for your reply.

 I'm using Linux 2.2.16-22.
 The exact error that appears is:

 /usr/local/etc/httpd/httpd: error in loading shared libraries:
 libmysqlclient.so.6: cannot open shared object file: No such file or
directory

 In MySQL version 3.22.32 there was a file ./lib/mysql/libmysqlclient.so.6

 I suppose in MySQL version 3.23.32 this file corresponds to
 ./lib/libmysqlclient.a

 I thought it would be smart to alias link ./lib/mysql/libmysqlclient.so.6
 to ./lib/libmysqlclient.a.
 But when I restart httpd, the following error appears

 /usr/local/etc/httpd/httpd: error in loading shared libraries:
 libmysqlclient.so.6: invalid ELF header

 I hope you can help me with this information.

 Thanks,

 Tim Samshuijzen



 At 22:21 3-2-2001 -0200, you wrote:
 I suppose your operational system is Windows... Or is it Linux? Regarding
 Windows at least, and I believe Linux also, MySQL has got nothing to do
with
 your problem. If PHP is not working on your system, probabily this is
 something related to your web server. Are you sure the correct MIME types
 are being used? Please give as much information as you can. Otherwise
it's
 very difficult to come to a conclusion.
 
 BR,
 
 Fbio Ottolini
 
 P.S.: Consider upgrading to PHP4.04Pl1.
 
 - Original Message -
 From: "Tim Samshuijzen" [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Saturday, February 03, 2001 10:02 PM
 Subject: PHP does not work after upgrading MySQL to 3.23.32
 
 
 
 
  Hi,
 
  I have just upgraded from MySQL 3.22.32 to 3.23.32. Now PHP3 does
  not work. I've tried restarting httpd, but an error appears "file
  not found", and httpd fails to start up. HELP!
 
  What am I doing wrong?
 
  Thanks,
 
  Tim Samshuijzen
 
 
 
 
  -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
 
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail
 [EMAIL PROTECTED]
  Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 
 
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 
 


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How to select the 10 last items from a table?

2001-01-31 Thread Tim Samshuijzen 



I think the only way to do it is first get the amount of items:

   SELECT COUNT(*) FROM item;

if you use PHP, store the count in a variable called $count.
subtract 10 from count:

$limit = 10;
if($count  $limit) {
  $limit = $count;
}
$count = $count - 10;
if($count  0) {
  $count = 0;
}

then do the following:

   SELECT * FROM item ORDER BY id LIMIT $count,$limit;


Tim Samshuijzen



At 09:03 31-1-2001 -0600, you wrote:
Remco van den Berg wrote:
 
 On Wed, Jan 31, 2001 at 08:00:23AM -0600, The Tilghman wrote:
  Try sorting in DESCending order.
 
  SELECT * FROM item ORDER BY id DESC LIMIT 10;
 
  -Tilghman
 
 People,
 
 Thanks for all the answers, but the question was:
 
   "How to get the last 10 items in incrementing order."
 
 Please read thread before answering.
 
 -Remco

I've been reading the thread.
You were told how to get the last 10 records.
That is the only way  to get them.
Unfortunately, that means they will be in reverse order.
You must be reading them into an array somewhere, so you
can easily access them in either order once you have stored them.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

same file size after DELETE

2001-01-29 Thread Tim Samshuijzen 



Hello,

I have just deleted thousands of records but the file
is still the same size as before. How do I make the
file compact? i.e. how do I get rid of all the "empty
spaces" in the table?

Tim Samshuijzen




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MyFULLTEXT search

2001-01-26 Thread Tim Samshuijzen 



Hello,

For the company I work for I have set up a MySQL database for a 
books website with more that 1 million records of books. 
The visitor can search for keywords. A sequential scan would be
much too slow. (When I made the database, I had not yet heard of 
the new FULLTEXT index in MySQL.) To speed up searches I placed 
every word of every collumn in a seperate table called 'words'. 

table: words
--
| WordNumber  | Word |
|-
| ..  | ..   |
| 200 | 'jack'   |
| 201 | 'road'   |
| ..  | ..   |
--

I created another table called 'links' with references to the 
books table:

table: links

| WordNumber  | BookNumber |
|---
| ..  | .. |
| 201 | 258374 |
| 675 | 578524 |
| ..  | .. |


The 'books' table looks something like this:

table: books
-
| BookNumber  | Author  | Title | ...
|
| ..  | ..  | ...   | ...
| 258374  | 'Kerouac, Jack' | 'On the Road' | ...
| ..  | ..  | ...   | ...
-

As records are added and deleted from the books table,
I make sure the 'words' table and the 'links' table are
updated.

As an example, when the visitor searches for the keywords 
"jack kerouac road", then the following SQL statement is 
created:

SELECT B.* FROM books AS B, words AS XL, links AS YL WHERE 
  (XL.xWord LIKE 'jack%' OR 
   XL.xWord LIKE 'kerouac%' OR 
   XL.xWord LIKE 'road%') AND 
  YL.WordNumber = XL.WordNumber AND 
  YL.BookNumber = B.BookNumber;

The website is getting incredibly busy and some searches
are getting slow. 
Also, the links table is getting very big (more than
30 million records!).
If it continues to grow like it does now, we will end up in 
problems in the near future.

Could anyone give me advice on how to increase performance?
Any help is much appreciated.

Thanks!

Tim Samshuijzen






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MyFULLTEXT search

2001-01-26 Thread Tim Samshuijzen 



Hello,

For the company I work for I have set up a MySQL database for a 
books website with more that 1 million records of books. 
The visitor can search for keywords. A sequential scan would be
much too slow. (When I made the database, I had not yet heard of 
the new FULLTEXT index in MySQL.) To speed up searches I placed 
every word of every collumn in a seperate table called 'words'. 

table: words
--
| WordNumber  | Word |
|-
| ..  | ..   |
| 200 | 'jack'   |
| 201 | 'road'   |
| ..  | ..   |
--

I created another table called 'links' with references to the 
books table:

table: links

| WordNumber  | BookNumber |
|---
| ..  | .. |
| 201 | 258374 |
| 675 | 578524 |
| ..  | .. |


The 'books' table looks something like this:

table: books
-
| BookNumber  | Author  | Title | ...
|
| ..  | ..  | ...   | ...
| 258374  | 'Kerouac, Jack' | 'On the Road' | ...
| ..  | ..  | ...   | ...
-

As records are added and deleted from the books table,
I make sure the 'words' table and the 'links' table are
updated.

As an example, when the visitor searches for the keywords 
"jack kerouac road", then the following SQL statement is 
created:

SELECT B.* FROM books AS B, words AS XL, links AS YL WHERE 
  (XL.xWord LIKE 'jack%' OR 
   XL.xWord LIKE 'kerouac%' OR 
   XL.xWord LIKE 'road%') AND 
  YL.WordNumber = XL.WordNumber AND 
  YL.BookNumber = B.BookNumber;

The website is getting incredibly busy and some searches
are getting slow. 
Also, the links table is getting very big (more than
30 million records!).
If it continues to grow like it does now, we will end up in 
problems in the near future.

Could anyone give me advice on how to increase performance?
Any help is much appreciated.

Thanks!

Tim Samshuijzen






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php