GROUP BY with LIMIT -- an Optimizer Bug?
Hi, When you have a table with more than 10 million rows, one row less or one row more shouldn't make a difference, right? ... Well, think again. Look at this EXPLAIN statements, it seems that the optimizer prefers a full table scan in the normal query but uses the index if there's a LIMIT *number_of_rows* - 1! Is this a bug in the optimizer? BTW, the version of mysql is 4.0.16-nt. Thanks in advance, Hector mysql explain select tel, sum(minutos) as minutos from hist_in group by 1; +-+--+---+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+--+-+ | hist_in | ALL | NULL | NULL |NULL | NULL | 10618816 | Using temporary; Using filesort | +-+--+---+--+-+--+--+-+ 1 row in set (0.01 sec) mysql explain select tel, sum(minutos) as minutos from hist_in group by 1 limit 10618815; +-+---+---+--+-+--+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+--+-+--+--+---+ | hist_in | index | NULL | tel | 42 | NULL | 10618816 | | +-+---+---+--+-+--+--+---+ 1 row in set (0.00 sec) mysql explain select tel, sum(minutos) as minutos from hist_in group by 1 limit 10618816; +-+--+---+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+--+-+ | hist_in | ALL | NULL | NULL |NULL | NULL | 10618816 | Using temporary; Using filesort | +-+--+---+--+-+--+--+-+ 1 row in set (0.00 sec) mysql show index from hist_in; +-++--+--+-+---+-+--++--+--- -+-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | In dex_type | Comment | +-++--+--+-+---+-+--++--+--- -+-+ | hist_in | 1 | tel |1 | tel | A | 1179868 | NULL | NULL | YES | BT REE | | | hist_in | 1 | tel |2 | telefb | A |10618816 | NULL | NULL | YES | BT REE | | | hist_in | 1 | tel |3 | rutaentran | A |10618816 | NULL | NULL | YES | BT REE | | | hist_in | 1 | tel |4 | rutasalien | A |10618816 | NULL | NULL | YES | BT REE | | +-++--+--+-+---+-+--++--+--- -+-+ 4 rows in set (0.22 sec) mysql explain hist_in; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | tel| char(8) | YES | MUL | NULL| | | telefb | char(14) | YES | | NULL| | | rutaentran | char(8) | YES | | NULL| | | rutasalien | char(8) | YES | | NULL| | | minutos| int(7) | YES | | NULL| | ++--+--+-+-+---+ 5 rows in set (0.17 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warnings in CREATE TABLE
Hi all, Could you please tell me what can be causing this warnings? How can I see them? My Python script is dying because of this warnings :( Since I'm using 4.0.16, I can't use SHOW WARNINGS. Thanks in advance, Hector mysql create table new_web select a.* from otr_new as a join internet as b on a.telefb = b.tel; Query OK, 25335 rows affected (46.98 sec) Records: 25335 Duplicates: 0 Warnings: 311 mysql explain select a.* from otr_new as a join internet as b on a.telefb = b.tel; +---+---+---+--+-+--+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+---+---+--+-+--+-+--+ | a | index | NULL | tel | 47 | NULL | 3343166 | Using index | | b | ref | tel | tel | 8 | a.telefb | 11 | Using where; Using index | +---+---+---+--+-+--+-+--+ 2 rows in set (0.00 sec) mysql select version(); +---+ | version() | +---+ | 4.0.16-nt | +---+ 1 row in set (0.02 sec) mysql explain otr_new; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | tel| char(8) | YES | MUL | NULL| | | telefb | char(14) | YES | | NULL| | | rutaentran | char(8) | YES | | NULL| | | rutasalien | char(8) | YES | | NULL| | | minutos| int(7) | YES | | NULL| | ++--+--+-+-+---+ 5 rows in set (0.00 sec) mysql explain internet; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | tel | char(7) | YES | MUL | NULL| | | modem | char(30) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) mysql explain new_web; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | tel| char(8) | YES | | NULL| | | telefb | char(14) | YES | | NULL| | | rutaentran | char(8) | YES | | NULL| | | rutasalien | char(8) | YES | | NULL| | | minutos| int(7) | YES | | NULL| | ++--+--+-+-+---+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Warnings in CREATE TABLE
Héctor Villafuerte D. wrote: Hi all, Could you please tell me what can be causing this warnings? How can I see them? My Python script is dying because of this warnings :( Since I'm using 4.0.16, I can't use SHOW WARNINGS. Thanks in advance, Hector mysql create table new_web select a.* from otr_new as a join internet as b on a.telefb = b.tel; Query OK, 25335 rows affected (46.98 sec) Records: 25335 Duplicates: 0 Warnings: 311 mysql explain select a.* from otr_new as a join internet as b on a.telefb = b.tel; +---+---+---+--+-+--+-+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra| +---+---+---+--+-+--+-+--+ | a | index | NULL | tel | 47 | NULL | 3343166 | Using index | | b | ref | tel | tel | 8 | a.telefb | 11 | Using where; Using index | +---+---+---+--+-+--+-+--+ 2 rows in set (0.00 sec) mysql select version(); +---+ | version() | +---+ | 4.0.16-nt | +---+ 1 row in set (0.02 sec) mysql explain otr_new; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | tel| char(8) | YES | MUL | NULL| | | telefb | char(14) | YES | | NULL| | | rutaentran | char(8) | YES | | NULL| | | rutasalien | char(8) | YES | | NULL| | | minutos| int(7) | YES | | NULL| | ++--+--+-+-+---+ 5 rows in set (0.00 sec) mysql explain internet; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | tel | char(7) | YES | MUL | NULL| | | modem | char(30) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) mysql explain new_web; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | tel| char(8) | YES | | NULL| | | telefb | char(14) | YES | | NULL| | | rutaentran | char(8) | YES | | NULL| | | rutasalien | char(8) | YES | | NULL| | | minutos| int(7) | YES | | NULL| | ++--+--+-+-+---+ 5 rows in set (0.00 sec) hmmm, a pretty strange behaviour we have here Look what I did: (1) Increase the length of 'internet.tel' field (so it matches 'otr_new.telefb') mysql explain internet; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | tel | char(14) | YES | MUL | NULL| | | modem | char(30) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) (2) Run the query... and voila! mysql create table tmp_web3 select a.* from otr_new as a join internet as b ON a.telefb = b.tel; Query OK, 25335 rows affected (49.02 sec) Records: 25335 Duplicates: 0 Warnings: 0 The number of records found are the same in both queries, but this one has no warnings! Am I missing something obvious here? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replacing Multiple Subqueries
Hi all, I know how to do this query with subqueries like this: select * from traf_oper where rutasalien in (select ruta_salid from rutas where codigo_ope = 0) and rutaentran in (select ruta_salid from rutas where codigo_ope 0) -- The table structures is like this: mysql explain traf_oper; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | tel| char(8) | YES | MUL | NULL| | | fecha | char(8) | YES | | NULL| | | hora | char(6) | YES | | NULL| | | telefb | char(14) | YES | | NULL| | | tiempotasa | char(6) | YES | | NULL| | | rutasalien | char(7) | YES | | NULL| | | rutaentran | char(7) | YES | | NULL| | | serie | char(3) | YES | | NULL| | | tipotraf | int(1) | YES | | NULL| | | minutos| int(4) | YES | | NULL| | ++--+--+-+-+---+ 10 rows in set (0.44 sec) mysql explain rutas; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | CODIGO_TRA | int(6) | YES | | NULL| | | RUTA_SALID | char(20) | YES | MUL | NULL| | | DESCRIPCIO | char(20) | YES | | NULL| | | CODIGO_CIR | int(6) | YES | | NULL| | | TIPO_RUTA | char(20) | YES | | NULL| | | SISTEMA_TA | int(6) | YES | | NULL| | | CODIGO_OPE | int(6) | YES | | NULL| | | CORRELATIV | int(6) | YES | | NULL| | ++--+--+-+-+---+ 8 rows in set (0.08 sec) -- I tried to do this: mysql explain select a.* from traf_oper a join rutas b on a.rutasalien = b.ruta_salid where b.codigo_ope = 0 and a.rutaentran = b.ruta_salid where b.codigo_ope 0; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where b.codigo_ope 0' at line 1 How can I substitute multiple subqueries with JOIN's? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replacing Multiple Subqueries
Héctor Villafuerte D. wrote: Hi all, I know how to do this query with subqueries like this: select * from traf_oper where rutasalien in (select ruta_salid from rutas where codigo_ope = 0) and rutaentran in (select ruta_salid from rutas where codigo_ope 0) -- I tried to do this: mysql explain select a.* from traf_oper a join rutas b on a.rutasalien = b.ruta_salid where b.codigo_ope = 0 and a.rutaentran = b.ruta_salid where b.codigo_ope 0; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'where b.codigo_ope 0' at line 1 How can I substitute multiple subqueries with JOIN's? Thanks in advance. Hi guys, just to let you know how I solved it! select a.* from traf_oper a join rutas r1 on a.rutasalien = r1.ruta_salid and r1.codigo_ope = 0 join rutas r2 on a.rutaentran = r2.ruta_salid and r2.codigo_ope 0 Nice, isn't it? :) Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOWing temporary tables
Hi all, How can I see the temporary tables in a database? Is there something like SHOW TEMPORARY TABLES? Thanks, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting Corrupt MyISAM Table
Héctor Villafuerte D. wrote: Hi all, In the following lines I'll try to describe the situation the best I can. Thanks in advance for your help! Hector - Here's the situation: * I have repeatedly got a corrupt table (which is pretty big, like 25M rows) * MyISAM tables (the one with the problem is *otr_hist*) * MySQL 4.0.16-nt * Windows XP PRO (using NTFS, so file size shouldn't be a problem, right?) - Here are some of the messages from MySQL: mysql show tables; ERROR 2013: Lost connection to MySQL server during query mysql show tables; ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:5 Current database: otr +---+ | Tables_in_otr | +---+ | otr_hist | | otr_new | | otr_tmp1 | | otr_tmp2 | +---+ 4 rows in set (0.78 sec) mysql show processlist; ++++--+-+---+---+--+ | Id | User | Host | db | Command | Time | State | Info | ++++--+-+---+---+--+ | 4 | villaf | localhost:1884 | otr | Sleep | 26130 | | NULL | | 5 | villaf | localhost:1997 | otr | Query | 0 | NULL | show processlist | ++++--+-+---+---+--+ - Here's the table structure: CREATE TABLE `otr_hist` ( `tel` char(8) default NULL, `telefb` char(14) default NULL, `rutaentran` char(7) default NULL, `rutasalien` char(7) default NULL, `minutos` int(16) default NULL, KEY `tel` (`tel`,`telefb`,`rutaentran`,`rutasalien`,`minutos`) ) TYPE=MyISAM - Here's the error: mysql show index from otr_hist; ERROR 1016: Can't open file: 'otr_hist.MYI'. (errno: 144) - Here's the python script which should be doing all the job, I think it's pretty easy to follow, if you like you can see the thread Additive UPDATE: # Operators Traffic Report (otr) # Hector Villafuerte D. # 20031106 # # otr.py : v.0.1 : Prepares the monthly Operators Traffic Report import MySQLdb, string, os db = MySQLdb.connect(host = localhost, user = villaf, passwd = secret, db = otr) cursor = db.cursor() def update_hist(csv): # Loads new data cursor.execute(TRUNCATE TABLE otr_new) cursor.execute(ALTER TABLE otr_new DISABLE KEYS) cursor.execute(LOAD DATA INFILE '%s' INTO TABLE otr_new FIELDS TERMINATED BY ',' ENCLOSED BY '\' IGNORE 1 LINES % (csv)) cursor.execute(ALTER TABLE otr_new ENABLE KEYS) # Intersection between *otr_hist* *otr_new* cursor.execute(DROP TABLE IF EXISTS otr_tmp1) cursor.execute(create table otr_tmp1 select a.tel, a.telefb, a.rutaentran, a.rutasalien, a.minutos + b.minutos as minutos from otr_hist as a join otr_new as b using(tel, telefb, rutaentran, rutasalien)) cursor.execute(ALTER TABLE otr_tmp1 ADD INDEX (tel, telefb, rutaentran, rutasalien, minutos)) # Difference between *otr_new* *otr_hist* cursor.execute(DROP TABLE IF EXISTS otr_tmp2) cursor.execute(create table otr_tmp2 select a.* from otr_new as a left join otr_hist as b using(tel, telefb, rutaentran, rutasalien) where b.minutos is null) # Updates *otr_hist* with *otr_tmp1* cursor.execute(UPDATE otr_hist AS A, otr_tmp1 AS B SET A.minutos = B.minutos WHERE A.tel = B.tel AND A.telefb = B.telefb AND A.rutaentran = B.rutaentran AND A.rutasalien = B.rutasalien) # Updates *otr_hist* with *otr_tmp2* cursor.execute(ALTER TABLE otr_hist DISABLE KEYS) cursor.execute(INSERT INTO otr_hist SELECT * FROM otr_tmp2) cursor.execute(ALTER TABLE otr_hist ENABLE KEYS) update_hist(c:tmp20031014.csv) update_hist(c:tmp20031015.csv) update_hist(c:tmp20031016.csv) update_hist(c:tmp20031017.csv) update_hist(c:tmp20031018_21.csv) update_hist(c:tmp20031022.csv) update_hist(c:tmp20031023.csv) update_hist(c:tmp20031024.csv) update_hist(c:tmp20031025_27.csv) update_hist(c:tmp20031028.csv) update_hist(c:tmp20031029.csv) update_hist(c:tmp20031030.csv) update_hist(c:tmp20031031.csv) update_hist(c:tmp20031101.csv) - And here are the questions :) * Is this a bug in 4.0.16? * Would you suggest me another approach for this task, in order to avoid the corruption? - Strange stuff! I've just noticed this weird behaviour: I have cygwin so I did a *ls -l* and a *dir* in the database directory. Now see the size of *otr_hist.MYI* in *ls*! Weird, isn't it? C:\mysql\data\otrdir Volume in drive C has no label. Volume Serial Number is 4C83-B7BD Directory of C:\mysql\data\otr 11/11/2003 08:45 AMDIR . 11/11/2003 08:45 AMDIR .. 06/11/2003 05:50 PM 8,682 otr_hist.frm 11/11/2003 01:22 AM 1,089,602,716 otr_hist.MYD 11/11/2003 01:22 AM
Re: Additive UPDATE
Héctor Villafuerte D. wrote: Hi all! I need to perform what I've called an additive UPDATE. The logic is the next: (1) There's a historic table (HISTORY) with two fields: mysql create table history (ID char(7) primary key, VAL int(12)); (2) There's a new table everyday (TODAY) with exactly the same structure as HISTORY (ID and VAL). (3) I need to feed HISTORY with the values found in TODAY in an additive way. I think that the pseudocode would be like this: * IF TODAY.id EXISTS IN HISTORY.id - THEN UPDATE HISTORY.val = HISTORY.val + TODAY.val * ELSE UPDATE HISTORY.id = TODAY.id, HISTORY.val = TODAY.val ... you see now why I called it an additive UPDATE? :) Is there a way to perform this with just MySQL or do I need to combine it with a programming language? Thanks in advance, Hector Ok, here's what I've done so far... maybe someone could find this useful :) The magic is in understanding JOIN's! The main reason why I installed MySQL 4.1.0-alpha was because of sub-selects (since I had no idea they where special cases of JOIN's and they are slower than JOIN's too!) So, I'll try to use REPLACE later (so I don't have to query the last UNION SELECT). Of course, any comments are welcome! Hector # [mysql_localhost] Query Window # Connection: mysql_localhost # Host: localhost # Saved: 2003-11-05 11:45:25 # # Query: # select a.tel, a.telefb, a.rutaentran, a.rutasalien, a.minutos + b.minutos as total # from grp_oper_hist as a join grp_oper_hoy as b using(tel, telefb, rutaentran, rutasalien) # union # select a.* from grp_oper_hoy as a left join grp_oper_hist as b using(tel, telefb, rutaentran, rutasalien)where b.minutos is null # union # select a.* from grp_oper_hist as a left join grp_oper_hoy as b using(tel, telefb, rutaentran, rutasalien)where b.minutos is null # 'tel','telefb','rutaentran','rutasalien','total' '000','120','PCS27LI','PAR37UO','4' '000','122','PCS27LI','CEN47UO','2' '000','123','PCS27LI','GDV57UO','6' '0006429','123','BELL7CI','GDV57UO','3' '000','110','PCS27LI','PAR37UO','3' '287','120','BELL7CI','PAR37UO','13' '287','123','BELL7CI','GDV57UO','2' '0002407','123','PCS27LI','GDV57UO','3' '0003076','123','BELL7CI','GDV57UO','2' '0006429','123','PCS27LI','GDV57UO','1' '0009210','122','BELL7CI','CEN47UO','1' . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Additive UPDATE
Hi all! I need to perform what I've called an additive UPDATE. The logic is the next: (1) There's a historic table (HISTORY) with two fields: mysql create table history (ID char(7) primary key, VAL int(12)); (2) There's a new table everyday (TODAY) with exactly the same structure as HISTORY (ID and VAL). (3) I need to feed HISTORY with the values found in TODAY in an additive way. I think that the pseudocode would be like this: * IF TODAY.id EXISTS IN HISTORY.id - THEN UPDATE HISTORY.val = HISTORY.val + TODAY.val * ELSE UPDATE HISTORY.id = TODAY.id, HISTORY.val = TODAY.val ... you see now why I called it an additive UPDATE? :) Is there a way to perform this with just MySQL or do I need to combine it with a programming language? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Beating Rushmore! -- was: Really slow query (compared with Visual FoxPro)
Alexander Barkov wrote: Héctor, Changing various buffer sizes can improve performance significantly, with key_buffer_size as the first thing to tune. Also, make sure you are using not debugging version of the server. Please also take a look in this section of the documentation for details: http://www.mysql.com/doc/en/Optimising_the_Server.html Hi guys! Ok, just to report my findings (and MySQL achievements). I added this lines to my.ini (in the Windows directory): set-variable=key_buffer_size=128M set-variable=read_rnd_buffer_size=16M set-variable=join_buffer_size=16M set-variable=sort_buffer_size=16M and, after this changes, the winner is MySQL! It is almost 10% faster than VFP! (and it is easier for me to use it within Python!) Thank you all for your help, since this was my first time at this list! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Almost beating Rushmore -- was: Really slow query (compared with Visual FoxPro)
Hi guys! Ok, I'm closer to beat Rushmore (VFP optimizer) now! After some reading about MySQL optimization techniques, here is the summary of what I've done: 1. Add a compound index to the table 2. Use EXPLAIN to check out the query (with GROUP BY on multiple fields) 3. Create the summary table And here's the detailed instructions: mysql alter table traf_oper add index (tel, telefb, rutaentran, rutasalien, minutos); Query OK, 5067215 rows affected (5 min 22.36 sec) Records: 5067215 Duplicates: 0 Warnings: 0 mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; ++-+---+---+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | index | NULL | tel | 45 | NULL | 5067215 | Using index | ++-+---+---+---+--+-+--+-+-+ 1 row in set (0.03 sec) mysql create table grp_oper select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3326541 rows affected (33.81 sec) Records: 3326541 Duplicates: 0 Warnings: 0 Adding the times together it would take MySQL like 6 minutes (VFP does it in about 4 minutes). I still haven't tweaked some server variables (read_rnd_buffer_size, sort_buffer_size, max_join_size), but, as always, I'll keep trying :) Thanks in advance for your comments and suggestions, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Chris wrote: Hmm It's just occured to me that you're basically copying and entire table from one place to another. If I recall correctly, FoxPro cheats somewhat in this situation - it just copies the concerned files! Which table type are you using (something I should have asked in the beginning)? Can we see the output of SHOW CREATE TABLE table_name; ? Regards, Chris Hi, I'm using MyISAM tables. BTW, I'm almost done with the optimization chapter from the manual... and I haven't had one of those Eureka! moments... anyway I'll keep trying... any more suggestions? Thanks again. Hector Here's an example of what you requested: mysql show create table tmp; +---++ | Table | Create Table | +---++ | tmp | CREATE TABLE `tmp` (`tel` int(11) default NULL) TYPE=MyISAM CHARSET=latin1 | +---++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Mojtaba Faridzad wrote: BUT my experience: try to change the logic of your report not to retrieve large number of records. user LIMIT to create the reports page by page. this is the best and even better for the user. Ok thanks, but how exactly do I change-the-logic of this query: mysql select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; I also found this in the manual: Note that in some cases MySQL will not use an index, even if one would be available. Some of the cases where this happens are: * If the use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as this will require us to do much fewer seeks.) Note that if such a query uses |LIMIT| to only retrieve part of the rows, MySQL will use an index anyway, as it can much more quickly find the few rows to return in the result. So, it seems that I REALLY need to change the logic of this query, since it obviusly would do a table scan. Any help would be greatly appreciated! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing GROUP BY
Hi! I've found this in: http://www.mysql.com/information/presentations/presentation-oscon2000-2719/ Instead of doing a lot of |GROUP BY|s on a big table, create summary tables of the big table and query this instead. Would you please tell me how to create summary tables that can help me get this query info: mysql select tel, telefb, rutaentran, rutasalien, sum(minutos) from traf_oper group by 1, 2, 3, 4; This is the table info: mysql describe traf_oper; ++--+---+--+-+-+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+-+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+-+---+ Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Really slow query (compared with Visual FoxPro)
Hi all, I'm migrating from Visual FoxPro (I'm a newbie). On a daily basis I need to run this query on a table with a little more than 5 million records. + MS Visual FoxPro 6.0 select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4 into table oper_grp This took about 173 seconds (let's say 3 minutes) + MySQL 4.1.0-alpha-max-nt mysql create table oper_grp select tel, telefb, rutaentran, rutasalien, - sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; Query OK, 3301017 rows affected (8 hours 36 min 48.83 sec) --- 516 minutes! Records: 3301017 Duplicates: 0 Warnings: 0 Why is there such a big difference?! Any optimization tips I could follow? Thanks in advance, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Chris Nolan wrote: Hi! Given VFP's internals (I have to support FoxPro 2.6 apps, oh the pain of it all!), MySQL should be capable of much better performance, considering the only thing FoxPro has in terms of a possible advantage is Rushmore. Which indexes do you have on the tables in the query? Regards, Chris Indexes? hmmm... I knew those were useful for something :) In Visual FoxPro I don't use indexes for this table... so I didn't considered them necessary in MySQL (now I think I need to get to the basics of RDBMS). This table I'm talking about is a CDR (call detail record), so a record represents a call from a given telephone. I'm surely wrong but, ain't the index suppossed to be on a field with distinct entries? Does VFP automagically creates indexes depending on the query? As you can see, I would greatly appreciate any pointers to some database theory. Thanks again! Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really slow query (compared with Visual FoxPro)
Dan Greene wrote: Indexes are your friend. Perhaps your best friend (as far as databases go) --- To give light on your question... you can index any field... if the entries are distinct, it's called a 'unique index' which are the best kind to use. Otherwise you have a 'non-unique index', which can also be handy Think of indexes, like... well... book indexes... to prevent you from looking all over the book for an answer, you can find the exact page the topic is on (unique index), or if it's covered in multiple pages, you reduce your searching to just that list of pages (non-unique index). Then there's compound indexes, which is an index on the value of two seperate fields (which can be unique, or non-unique). As a rule of thumb, you want indexes on your unique key (primary key) (call record id) Most db's make indexes automatically on keys you say are the primary key. Also on your most commonly searched fields (usually non-unique) such as foreign keys (call record id in your record notes table for example) -- Ok, I've found the optimization chapter in the manual and I'm still reading it. Here's what I've done so far: mysql alter table traf_oper add index (tel), add index (telefb); mysql explain select tel, telefb, rutaentran, rutasalien, sum(minutos) as minutos from traf_oper group by 1, 2, 3, 4; ++-+---+--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+-+--+-+-+ | 1 | SIMPLE | traf_oper | ALL | NULL | NULL |NULL | NULL | 5014313 | Using temporary; Using filesort | ++-+---+--+---+--+-+--+-+-+ 1 row in set (0.50 sec) mysql describe traf_oper; ++--+---+--+-+-+---+ | Field | Type | Collation | Null | Key | Default | Extra | ++--+---+--+-+-+---+ | tel| char(8) | latin1_swedish_ci | YES | MUL | NULL| | | fecha | char(8) | latin1_swedish_ci | YES | | NULL| | | hora | char(6) | latin1_swedish_ci | YES | | NULL| | | telefb | char(14) | latin1_swedish_ci | YES | MUL | NULL| | | tiempotasa | char(6) | latin1_swedish_ci | YES | | NULL| | | rutasalien | char(7) | latin1_swedish_ci | YES | | NULL| | | rutaentran | char(7) | latin1_swedish_ci | YES | | NULL| | | serie | char(3) | latin1_swedish_ci | YES | | NULL| | | tipotraf | int(1) | binary| YES | | NULL| | | minutos| int(4) | binary| YES | | NULL| | ++--+---+--+-+-+---+ 10 rows in set (0.00 sec) So, why isn't SELECT using indexes (key = NULL, key_len = NULL)? Where else do I need to add indexes? Thanks for your help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
