problem with queries
hello All,
can anybody tell me how to correct this problem:
1. I use queries like that:
select /*! SQL_BUFFER_RESULT */ klient.logo, klient.klientid,
klient.klientnazwa, klient.struktura, concat(kodpocztowy,' ',miejscowosc)
miasto, aparatnumer, concat(ulicaskrot,' ',ulicanazwa,' ',posesja)
ulica,concat('woj. ',wojewodztwo,', powiat: ',powiat, ', gmina: ', gmina)
wojpow, klientbranze branza, email, www, wizytowka from klient [where conditions
or no conditions]
2. we upgraded PHP to 5.0.3, installed Zend Optimizer and upgraded MySQL from
4.0.20 to 4.1.7
3. after upgrading when I try to execute query from [1.] I have error:
MySQL Error: 1270 (Illegal mix of collations (latin2_general_ci,IMPLICIT),
(latin1_swedish_ci,COERCIBLE), (latin2_general_ci,IMPLICIT) for operation
'concat')
Session halted.
4. system variables are like this:
variable session global
back log 50 50
basedir /usr/local/mysql/ /usr/local/mysql/
binlog cache size 32768 32768
bulk insert buffer size 8388608 8388608
character set client latin1 latin2
character set connection latin1 latin2
character set database latin2 latin2
character set results latin1 latin2
character set server latin2 latin2
character set system utf8 utf8
character sets dir /usr/local/mysql/share/mysql/charsets/
/usr/local/mysql/share/mysql/charsets/
collation connection latin1_swedish_ci latin2_general_ci
collation database latin2_general_ci latin2_general_ci
collation server latin2_general_ci latin2_general_ci
how to proper correct that?
best regards in advance
Darek
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.296 / Virus Database: 265.6.0 - Release Date: 2004-12-17
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to proper set latin2 character set?
hi All, I have: character set latin1 latin1 character sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 in mysql settings I use Windows 2000 and IIS and mysql 4.0.22-nt-max how to change character set from latin1 to latin2 [to proper use Polish characters in sorting for example]? with regards DeRyl
Re: ORDER by date: reverse order
write: order by date desc DeRyl - Original Message - From: Jerry Swanson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 05, 2004 8:00 PM Subject: ORDER by date: reverse order I want to sort by date but the last date appears first. How to write such query? TH -- 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]
error with phpMyAdmin and MySQL 4.1.7
hi, I've just installed MySQL 4.1.7 when I try to launch phpMyAdmin I see this: Witamy w phpMyAdmin 2.5.5-pl1 phpMyAdmin próbowal polaczyc sie z serwerem MySQL, a serwer odrzucil polaczenie. Powinienes sprawdzic nazwe hosta, nazwe uzytkownika i haslo w pliku config.inc.php i upewnic sie, ze odpowiadaja one informacjom danym przez administratora serwera MySQL. Warning: Cannot modify header information - headers already sent by (output started at E:\www_files\phpmyadmin\libraries\auth\config.auth.lib.php:96) in E:\www_files\phpmyadmin\libraries\header_http.inc.php on line 14 Warning: Cannot modify header information - headers already sent by (output started at E:\www_files\phpmyadmin\libraries\auth\config.auth.lib.php:96) in E:\www_files\phpmyadmin\libraries\header_http.inc.php on line 15 Warning: Cannot modify header information - headers already sent by (output started at E:\www_files\phpmyadmin\libraries\auth\config.auth.lib.php:96) in E:\www_files\phpmyadmin\libraries\header_http.inc.php on line 16 Warning: Cannot modify header information - headers already sent by (output started at E:\www_files\phpmyadmin\libraries\auth\config.auth.lib.php:96) in E:\www_files\phpmyadmin\libraries\header_http.inc.php on line 17 Warning: Cannot modify header information - headers already sent by (output started at E:\www_files\phpmyadmin\libraries\auth\config.auth.lib.php:96) in E:\www_files\phpmyadmin\libraries\header_http.inc.php on line 20 Serwer localhost Blad MySQL zwrócil komunikat: #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client [Dokumentacja] in config.inc.php I have: $cfg['Servers'][$i]['auth_type'] = 'config'; // Authentication method (config, http or cookie based)? $cfg['Servers'][$i]['user'] = 'root'; // MySQL user $cfg['Servers'][$i]['password'] = 'my_password';// MySQL password (only needed // with 'config' auth_type) I also have MySQL Connector/ODBC 3.51 installed [windows 2000, php 5] when I earlier installed MySQL 5 beta it worked good... what's wrong and how to correct this? with regards DeRyl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
proper index creating
hi, I have some question about indexes more... in this example we have table just like this: table_tbl tableid int [primary key] tablefield1 tablefield2 ... tablefieldx tablefieldsort1 int tablefieldsort2 varchar(32) there are lots of questions containing order by clause like: select t.* from table_tbl t where condition 1... condition 2... condition x... order by tablefieldsort1 desc tablefirldsort2 asc my question is: what indexes must be created to be used in order by clause? they must speed up search and order by ... with regards DeRyl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with proper conditions and indexes needed
hi again, I have question like that: SELECT /*! SQL_BUFFER_RESULT */ DISTINCT branza.branzaid, branza.branzanazwa FROM branza, klientbranza, klientwojewodztwo WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid = klientwojewodztwo.klientid AND wojewodztwoid =9 ORDER BY bsort ASC and EXPLAIN SELECT /*! SQL_BUFFER_RESULT */ DISTINCT branza.branzaid, branza.branzanazwa FROM branza, klientbranza, klientwojewodztwo WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid = klientwojewodztwo.klientid AND wojewodztwoid =9 ORDER BY bsort ASC shows me: table type possible_keys key key_len ref rows Extra klientwojewodztwo ref kl_idx,woj_idx woj_idx 3 const 55054 Using where; Using temporary; Using filesort klientbranza ref branzaid,klientid klientid 8 klientwojewodztwo.klientid 1 branza ref id_na id_na 2 klientbranza.branzaid 1 but EXPLAIN SELECT /*! SQL_BUFFER_RESULT */ STRAIGHT_JOIN DISTINCT branza.branzaid, branza.branzanazwa FROM branza, klientbranza, klientwojewodztwo WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid = klientwojewodztwo.klientid AND wojewodztwoid =9 ORDER BY bsort ASC shows me: table type possible_keys key key_len ref rows Extra branza ALL id_na NULL NULL NULL 1451 Using temporary; Using filesort klientbranza ref branzaid,klientid branzaid 2 branza.branzaid 969 Distinct klientwojewodztwo ref kl_idx,woj_idx kl_idx 8 klientbranza.klientid 1 Using where; Distinct how to understand that? what should be the correct order in where clause and what indexes should be used? for this moment I have such indexes: table branza: bsort primary id_na(branzaid,branzanazwa) table klientbranza: branzaid klientid table klientwojewodztwo: klientid wojewodztwoid all these indexes are just index type [not unique or full...] how to correct interpret these explains and how to correct this? with regards DeRyl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with proper conditions and indexes needed
I'm really sorry wasting your time but... but - at first I read all materials I found [including this chapter you suggested] I can't correct interpret these explains I described - I've tried to change the order in where clause, making different indexes but see no changes and no correction so I think I don't understand that and I decided to write to the list with respect DeRyl - Original Message - From: [EMAIL PROTECTED] To: DeRyl [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 3:53 PM Subject: Re: help with proper conditions and indexes needed I really hate to do this to you but if I tried to answer your questions I would be copying from the book anyway. There is a section in the manual that deals specifically with query optimization. It covers index creation and usage, when an order by will and won't use and index and a bunch of other topics. Please review this material and come back to us if you need help understanding anything there. We will all happily do our best to make sense of whatever you are still having problems with. http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine DeRyl [EMAIL PROTECTED] wrote on 10/27/2004 09:31:58 AM: hi again, I have question like that: SELECT /*! SQL_BUFFER_RESULT */ DISTINCT branza.branzaid, branza.branzanazwa FROM branza, klientbranza, klientwojewodztwo WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid = klientwojewodztwo.klientid AND wojewodztwoid =9 ORDER BY bsort ASC and EXPLAIN SELECT /*! SQL_BUFFER_RESULT */ DISTINCT branza.branzaid, branza.branzanazwa FROM branza, klientbranza, klientwojewodztwo WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid = klientwojewodztwo.klientid AND wojewodztwoid =9 ORDER BY bsort ASC shows me: table type possible_keys key key_len ref rows Extra klientwojewodztwo ref kl_idx,woj_idx woj_idx 3 const 55054 Using where; Using temporary; Using filesort klientbranza ref branzaid,klientid klientid 8 klientwojewodztwo.klientid 1 branza ref id_na id_na 2 klientbranza.branzaid 1 but EXPLAIN SELECT /*! SQL_BUFFER_RESULT */ STRAIGHT_JOIN DISTINCT branza.branzaid, branza.branzanazwa FROM branza, klientbranza, klientwojewodztwo WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid = klientwojewodztwo.klientid AND wojewodztwoid =9 ORDER BY bsort ASC shows me: table type possible_keys key key_len ref rows Extra branza ALL id_na NULL NULL NULL 1451 Using temporary; Using filesort klientbranza ref branzaid,klientid branzaid 2 branza.branzaid 969 Distinct klientwojewodztwo ref kl_idx,woj_idx kl_idx 8 klientbranza.klientid 1 Using where; Distinct how to understand that? what should be the correct order in where clause and what indexes should be used? for this moment I have such indexes: table branza: bsort primary id_na(branzaid,branzanazwa) table klientbranza: branzaid klientid table klientwojewodztwo: klientid wojewodztwoid all these indexes are just index type [not unique or full...] how to correct interpret these explains and how to correct this? with regards DeRyl -- 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]
question about indexes
hello, I have a main table called client_tbl which contains: clientid bigint(14) [primary key] zoneid smallint(2) [secondary key] clientame varchar(255) clientaddress clientemail clientwww clientinfo clientamount decimal(6,1) and a lot of dictionaries that are int he same schema - so I describe them just like that: dictionary_tbl: dictionaryid int dictionaryword varchar(32) and dict_client_tbl: clientid dictionaryid all questions I use are just like that select c.* from dictionary_tbl d, dict_client_tbl k, client_tbl c where d.dictionary like 'some_eord_part%' and d.dictionaryid=k.dictionarytbl and k.clientid=c.clientid order by c.clientamount desc, c.clientname asc [! it is required sort order] there can be a few combinantions with more than one dictionaries my questions are: 1. how should be a good set of indexess on these tables [search speed is a primary requirement]? 2. how to optimize search speed? client table contains about 120 records disctionaries from 300 to 25 records what can I write more to better problem describe? regards DeRyl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about indexes
hi again,
I always must search within keywords that are in dictionary_tbl and always the
searched word must be beginning-of-string
maybe better solution is to use question like that:
select c.*
from dictionary_tbl d, dict_client_tbl k, client_tbl c
where left(d.dictionary,length('some_word_part'))= 'some_word_part'
and d.dictionaryid=k.dictionarytbl
and k.clientid=c.clientid
order by c.clientamount desc, c.clientname asc [! it is required sort order]
??
what do you think?
and index ont dict_client_tbl - you suggested an index on both fields...
is it important in which order the fields should be?
and what about indexes on client_tbl?
I think at least clientid must be a primary key
but I also often use zoneid in where clause
and fields clientamount and clientname are user in sort [order by clause]
what should I do with these fields and possible indexes?
maybe is better way to sort searched data [sort wastes always a lot of time, but
in this case it must be used]?
does anybody have a better idea?
regards
DeRyl
- Original Message -
From: [EMAIL PROTECTED]
To: DeRyl [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, October 26, 2004 6:17 PM
Subject: Re: question about indexes
You asked about indexes for these tables
I would make dictionaryid the PK of each dictionary_tbl.
I would add an index to each dictionary_tbl for dictionaryword.
I would make the PK for dict_client_tbl (clientid, dictionaryid)
You also asked about fastest possible searches.
Whenever you have to do a substring search (with LIKE or RLIKE) you
almost always eliminate any use of indexes. The index I suggested above is
great for beginning-of-string or whole-string matches (dictionaryword
LIKE 'wordstart%' or dictionaryword ='thisword' ) but it fails to
help for any other sub-string searches (dictionaryword LIKE '%endswith'
or dictionaryword LIKE %wordpart%)
Good Luck!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
DeRyl [EMAIL PROTECTED] wrote on 10/26/2004 10:19:21 AM:
hello,
I have a main table called client_tbl which contains:
clientid bigint(14) [primary key]
zoneid smallint(2) [secondary key]
clientame varchar(255)
clientaddress
clientemail
clientwww
clientinfo
clientamount decimal(6,1)
and a lot of dictionaries that are int he same schema - so I
describe them just
like that:
dictionary_tbl:
dictionaryid int
dictionaryword varchar(32)
and
dict_client_tbl:
clientid
dictionaryid
all questions I use are just like that
select c.*
from dictionary_tbl d, dict_client_tbl k, client_tbl c
where d.dictionary like 'some_eord_part%'
and d.dictionaryid=k.dictionarytbl
and k.clientid=c.clientid
order by c.clientamount desc, c.clientname asc [! it is required sort
order]
there can be a few combinantions with more than one dictionaries
my questions are:
1. how should be a good set of indexess on these tables [search speed is
a
primary requirement]?
2. how to optimize search speed?
client table contains about 120 records
disctionaries from 300 to 25 records
what can I write more to better problem describe?
regards
DeRyl
--
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]
great problem with questions
hello,
I have database with 30 tables [some have over 2000k some other over 4000k rec]
when I want to run a sql with a few conditions the answer is dramatically slow
[over 70 seconds!]
sql-s with one condition usually works well..
how is the corrcet way to optimize the database and sql questions?
the answer from sql should be under 1 second...
SHOW VARIABLES gives these informations:
Variable_name Value
back_log 50
basedir /usr/local/mysql/
bdb_cache_size 8388600
bdb_log_buffer_size 32768
bdb_home /dysk/mysql/data/
bdb_max_lock 1
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir /tmp/
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2...
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin2
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis...
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /dysk/mysql/data/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -()~*:|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb YES
have_crypt YES
have_innodb YES
have_isam YES
have_raid YES
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir ./
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language /usr/local/mysql/share/mysql/polish/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings ON
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1024
pid_file /dysk/mysql/data/mysqld.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 131072
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone CEST
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.20-max
version_comment Official MySQL-max binary
version_compile_os pc-linux
wait_timeout 28800
a good example of question is:
select distinct logo, klient.klientid, klientnazwa, struktura,
concat(kodpocztowy,' ',miejscowosc) miasto, aparatnumer, concat(ulicaskrot,'
',ulicanazwa,' ',posesja) ulica,concat('woj. ',wojewodztwo,' powiat: ',powiat)
wojpow, klientbranza branza, email, www, wizytowka
from klient, klientulice, klientulica, klientmiejscowosci, klientmiejscowosc,
branzaslowa, branzaslowo, klientbranza, klientslowa, klientslowo
where wojewodztwoid=7 AND klientulica.klientulica like'dwo%' AND klient.klientid
= klientulice.klientid AND klientulice.klientulicaid= klientulica.klientulicaid
AND klientmiejscowosc.klientmiejscowosc like'war%' AND klient.klientid =
klientmiejscowosci.klientid AND klientmiejscowosci.klientmiejscowoscid=
klientmiejscowosc.klientmiejscowoscid AND branzaslowo.branzaslowo like'sam%' AND
klient.klientid = klientbranza.klientid AND
klientbranza.branzaid=branzaslowa.branzaid AND branzaslowa.branzaslowoid=
branzaslowo.branzaslowoid AND (klientslowo.klientslowo LIKE 'sam%') AND
klient.klientid = klientslowa.klientid AND klientslowa.klientslowoid=
klientslowo.klientslowoid
can anyone help me with this?
with best regards
Darek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Re: great problem with questions
explain example sql question gives: table type possible_keys key key_len ref rows Extra klientslowo range PRIMARY,klientslowo klientslowo 40 NULL 351 Using where; Using temporary klientslowa ref klientslowoid klientslowoid 4 klientslowo.klientslowoid 19 Using index klient ref klientid,wojewodztwoid klientid 9 klientslowa.klientid 1 Using where klientmiejscowosci ref KLIENTMIEJSCOWOSCID,KLIENTID KLIENTID 9 klient.klientid 1 Using where; Distinct klientmiejscowosc eq_ref PRIMARY,KLIENTMIEJSCOWOSC PRIMARY 4 klientmiejscowosci.KLIENTMIEJSCOWOSCID 1 Using where; Distinct klientulice ref klientulicaid,klientid klientid 9 klient.klientid 1 Using where; Distinct klientbranza ref BRANZAID,KLIENTID KLIENTID 9 klient.klientid 2 Using where; Distinct branzaslowa ref branzaslowoid,branzaid branzaid 4 klientbranza.BRANZAID 3 Distinct branzaslowo eq_ref PRIMARY,branzaslowo PRIMARY 4 branzaslowa.branzaslowoid 1 Using where; Distinct klientulica eq_ref PRIMARY,klientulica PRIMARY 4 klientulice.klientulicaid 1 Using where; Distinct and the create table for explain tables: CREATE TABLE `branzaslowa` ( `branzaid` int(11) NOT NULL default '0', `branzaslowoid` int(11) NOT NULL default '0', KEY `branzaslowoid` (`branzaslowoid`), KEY `branzaid` (`branzaid`) ) TYPE=MyISAM; CREATE TABLE `branzaslowo` ( `branzaslowoid` int(11) NOT NULL auto_increment, `branzaslowo` varchar(32) default NULL, PRIMARY KEY (`branzaslowoid`), KEY `branzaslowo` (`branzaslowo`) ) TYPE=MyISAM AUTO_INCREMENT=1710 ; CREATE TABLE `klient` ( `klientid` bigint(14) default NULL, `klientnazwaorder` int(11) default NULL, `id_inst_nadrz` bigint(14) default NULL, `id_isnt_glown` bigint(14) default NULL, `wojewodztwoid` int(11) default NULL, `powiatid` int(11) default NULL, `gminaid` int(11) default NULL, `numerporzadkowy` int(11) default NULL, `klientnazwa` varchar(250) NOT NULL default '', `klientbranza` longtext, `ulicaskrot` varchar(20) default NULL, `ulicanazwa` varchar(255) default NULL, `posesja` varchar(100) default NULL, `miejscowosc` varchar(100) default NULL, `kodpocztowy` varchar(22) default NULL, `powiat` varchar(100) default NULL, `gmina` varchar(100) default NULL, `wojewodztwo` varchar(100) default NULL, `aparatnumer` longtext, `www` varchar(100) default NULL, `email` varchar(100) default NULL, `logo` varchar(100) default NULL, `wizytowka` varchar(100) default NULL, `wizytowkas` int(11) default NULL, `wizytowkaw` int(11) default NULL, `struktura` int(11) default NULL, `id_regionu` int(11) default NULL, `zrodlo` char(1) default NULL, `kwotareklam` double default NULL, KEY `klientid` (`klientid`), KEY `wojewodztwoid` (`wojewodztwoid`) ) TYPE=MyISAM; CREATE TABLE `klientbranza` ( `KLIENTID` bigint(14) default NULL, `BRANZAID` int(11) default NULL, `CZYPODSTAWOWA` int(11) default NULL, KEY `BRANZAID` (`BRANZAID`), KEY `KLIENTID` (`KLIENTID`) ) TYPE=MyISAM; CREATE TABLE `klientmiejscowosc` ( `KLIENTMIEJSCOWOSCID` int(11) NOT NULL default '0', `KLIENTMIEJSCOWOSC` char(64) default NULL, PRIMARY KEY (`KLIENTMIEJSCOWOSCID`), KEY `KLIENTMIEJSCOWOSC` (`KLIENTMIEJSCOWOSC`) ) TYPE=MyISAM; CREATE TABLE `klientmiejscowosci` ( `KLIENTID` bigint(14) default NULL, `KLIENTMIEJSCOWOSCID` int(11) default NULL, KEY `KLIENTMIEJSCOWOSCID` (`KLIENTMIEJSCOWOSCID`), KEY `KLIENTID` (`KLIENTID`) ) TYPE=MyISAM; CREATE TABLE `klientslowa` ( `klientid` bigint(14) NOT NULL default '0', `klientslowoid` int(11) NOT NULL default '0', KEY `klientslowoid` (`klientslowoid`,`klientid`) ) TYPE=MyISAM; CREATE TABLE `klientslowo` ( `klientslowoid` int(11) NOT NULL auto_increment, `klientslowo` varchar(40) NOT NULL default '', PRIMARY KEY (`klientslowoid`), KEY `klientslowo` (`klientslowo`) ) TYPE=MyISAM AUTO_INCREMENT=228453 ; CREATE TABLE `klientulica` ( `klientulicaid` int(11) NOT NULL default '0', `klientulica` char(96) default NULL, PRIMARY KEY (`klientulicaid`), KEY `klientulica` (`klientulica`) ) TYPE=MyISAM; CREATE TABLE `klientulice` ( `klientid` bigint(14) default NULL, `klientulicaid` int(11) default NULL, KEY `klientulicaid` (`klientulicaid`), KEY `klientid` (`klientid`) ) TYPE=MyISAM; --- Darek - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: DeRyl [EMAIL PROTECTED] Sent: Thursday, September 23, 2004 1:19 PM Subject: Re: great problem with questions * DeRyl I have database with 30 tables [some have over 2000k some other over 4000k rec] when I want to run a sql with a few conditions the answer is dramatically slow [over 70 seconds!] sql-s with one condition usually works well.. how is the corrcet way to optimize the database and sql questions? the answer from sql should be under 1 second... I agree. For a start, show us the output of EXPLAIN select distinct logo, klient.klientid ... Then we will probably need the output of SHOW CREATE TABLE for some of the tables, depending
Re: great problem with questions
The first thing to notice: Using temporary... this is to be avoided, if possible. ## how is the correct way to avoid that? The first table read is klientslowo based on the criteria klientslowo.klientslowo LIKE 'sam%'. Is this a reasonable approach to solving the query, giving your knowledge of the database structure and the data distribution? MySQL estimates that 351 records matches this criteria, is this close to the truth? If not, run ANALYZE TABLE. ## after analyze I saw: Table Op Msg_type Msg_text ikt.klientslowo analyze status Table is already up to date You should not need the DISTINCT keyword. There is no PRIMARY KEY in the branzaslowa table, it seems to have duplicates? ## it shouldn't so I'll try to select without DISTINCT You use a lot of bigint's... an unsigned int is usually sufficient, it consumes half the disc/RAM space... this could be important for the speed of the indexes. Optimally you would want all your indexes to fit in RAM, i.e. the sum of the size of the indexes should be smaller than your index buffer size (key_buffer_size). ## some of tables [like klient, klientslowa, klientulice, klientmiejscowosci etc...] uses in klientid field a numeric value which is 12,14 or 14 characters long so how to correct that? I took a look at the variables from your previous posting. You should increase the key_buffer_size, it is only 8M (default). If you only use MyISAM tables, try setting it to half your total available RAM. This will not affect the speed of the first few queries you run after a restart, but when the caches get hot you get very improved speed. ## how to change that from shell? I know Apache a little... regards in advance Darek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
