Re: ETA on 4.1
Terence wrote: > I asked the same question 2 or 3 months ago, and was told a week or > two. You're probably mistaking the (already happened) release of MySQL 4.1.0 with a "general availability" release of MySQL 4.1. -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ http://www.professionelle-softwareentwicklung-mit-php5.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ETA on 4.1
Petre Agenbag wrote: > 4.1 would be released in a week or 2 MySQL 4.1 will not be released in "a week or 2". My best guess is at the end of this year -- at the earliest. -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ http://www.professionelle-softwareentwicklung-mit-php5.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
.myd file weirdness
Recently I received a number of reports from users of my phpOpenTracker software about the following MySQL error Error: Can't open file: 'pot_visitors.MYD'. (errno: 145) The error goes away after repairing the table in question. Any idea how phpOpenTracker might trigger this? Thanks, Sebastian -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ http://www.professionelle-softwareentwicklung-mit-php5.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query, indexes not used
gerald_clark wrote: > You may not have enough rows to make it worthwhile to use an index. That may be true for the example query I posted that uses the Unit Test data, which is not much. A user of phpOpenTracker reports the same EXPLAIN result for the query performed on tabled with 1.000.000+ rows. -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ Did I help you? Consider a gift: http://wishlist.sebastian-bergmann.de/ - 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
Slow query, indexes not used
For this query
mysql> EXPLAIN
-> SELECT COUNT(accesslog.document_id) AS item_count,
-> data_table.stringAS item
->
->FROM pot_accesslog accesslog,
-> pot_visitors visitors,
-> pot_documents data_table
->
-> WHERE accesslog.accesslog_id = visitors.accesslog_id
-> AND accesslog.client_id= 1
-> AND accesslog.document_id = data_table.data_id
-> GROUP BY accesslog.document_id,
->data_table.string
-> ORDER BY item_count DESC;
+++--+-+-+---+--+--+
| table | type | possible_keys| key | key_len |
ref | rows | Extra|
+++--+-+-+---+--+--+
| accesslog | ALL| accesslog_id,client_time,document_id | NULL|NULL |
NULL |6 | Using where; Using temporary; Using filesort |
| visitors | index | PRIMARY | PRIMARY | 4 |
NULL |3 | Using where; Using index |
| data_table | eq_ref | PRIMARY | PRIMARY | 4 |
accesslog.document_id |1 | |
+++--+-+-+---+--+--+
3 rows in set (0.01 sec)
none of the possible indexes of the pot_accesslog table
CREATE TABLE pot_accesslog (
accesslog_id int(11) NOT NULL,
client_id int(10) unsignedNOT NULL,
timestamp int(10) unsignedNOT NULL,
document_id int(11) NOT NULL,
exit_target_idint(11) DEFAULT '0' NOT NULL,
entry_documentenum('0','1') NOT NULL,
KEY accesslog_id (accesslog_id),
KEY client_time (client_id, timestamp),
KEY document_id (document_id)
) DELAY_KEY_WRITE=1;
is used.
Any help on improving the performance of this would be appreciated,
Sebastian
--
Sebastian Bergmann
http://sebastian-bergmann.de/ http://phpOpenTracker.de/
Did I help you? Consider a gift: http://wishlist.sebastian-bergmann.de/
-
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: Proper use of indexes / keys
Roger Baklund wrote: > However, if the behaviour with no restrictions on duplicates is > correct, you may be better of without a primary key in this case. Okay, that's what I need here. > Also, the second create statement defines indexes on all fields, making > almost any query reasonably fast. That's what I need here, too. > You can almost always improve the performance of queries by adding an > index. The trick is to add the right index... :) If you for instance > often did a "SELECT * FROM pot_accesslog WHERE client_id=$id AND > timestamp > NOW() - INTERVAL 24 HOUR", you could add an index with > the involved fields: KEY client_time (client_id,timestamp). Would this perform different from a situation where I have separate indexes on both columns? > You should not create too many indexes, though. Inserts, updates and > deletes are slower when you have more indexes. You need to find > indexes that may be used by different queries: the client_time > (client_id,timestamp) index can also be used when you have a > client_id, but not the timestamp. This is because client_id is the > first part of the index. I know, therefore I use DELAY_KEY_WRITE=1. (Thanks to David & Kaj for pointing this out to me during lunch at the PHP International Conf) > CREATE TABLE pot_add_data ( > accesslog_id int(10) unsigned NOT NULL, > data_field varchar(32) NOT NULL, > data_value varchar(255) NOT NULL, > > PRIMARY KEY(accesslog_id), > KEY data_field (data_field), > KEY data_value (data_value) > ); > > Very often it is better to define this last index like this: > > KEY data_value (data_value(20)) I know, this was a leftover. I do not need that index at all and dropped it already. > I hope the above suggestions was usefull :) Thanks for your help, Sebastian -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ Did I help you? Consider a gift: http://wishlist.sebastian-bergmann.de/ - 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
Proper use of indexes / keys
I am currently trying to optimize the indexes / keys for the MySQL schema of phpOpenTracker. I'm wondering if multiple keys, like CREATE TABLE pot_accesslog ( accesslog_id int(10) unsigned NOT NULL, client_idint(10) unsigned NOT NULL, document_id int(11) NOT NULL, timestampint(10) unsigned NOT NULL, PRIMARY KEY (accesslog_id, client_id, document_id, timestamp) ); would be better than single keys for each column, like CREATE TABLE pot_accesslog ( accesslog_id int(10) unsigned NOT NULL, client_idint(10) unsigned NOT NULL, document_id int(11) NOT NULL, timestampint(10) unsigned NOT NULL, KEY accesslog_id (accesslog_id), KEY client_id(client_id), KEY document_id (document_id), KEY timestamp(timestamp) ); Have a look at http://www.sebastian-bergmann.de/single_keys.htm for a version of the schema that uses single keys, together with the output of EXPLAIN(query) for some of phpOpenTracker most commonly used queries. Have a look at http://www.sebastian-bergmann.de/multi_keys.htm for a version of the schema that uses multiple keys, together with the output of EXPLAIN(query) for some of phpOpenTracker most commonly used queries. It'd be really great if some kind soul could have a look at this and tell me what indexing strategy is suited best for this problem. Thanks & Greetings, Sebastian -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ Did I help you? Consider a gift: http://wishlist.sebastian-bergmann.de/ - 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 and MySQL 4.0.0
Michael Widenius wrote: > The symbol 'mysql_module_entry' doesn't come from the MySQL library, > so this is a PHP problem. Okay. > Note that you can use MySQL 4.0 with the old MySQL 3.23 library > without any problems. Yes, it works fine if I use the libmysql that is bundled with PHP. Only when I ./configure --with-mysql=/usr/local/mysql and link PHP against the MySQL 4.0.0 library this problem occurs. -- Sebastian Bergmann http://sebastian-bergmann.de/ http://phpOpenTracker.de/ Did I help you? Consider a gift: http://wishlist.sebastian-bergmann.de/ - 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
