MySQL Forums - MyISAM

why bigger index of ascii than utf8? (2 replies)

武士小浜 — Sun, 24 Mar 2013 06:29:25 +0000

Hello all,

I compared the size of index file of MyISAM
with charset=ascii and utf8,
as the followings:

create table TESTT_ASCII_test1 (
pid int,
pcode char(11),
primary key(pid, pcode)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

load data local infile 'test.dat'
into table TESTT_ASCII_test1;
flush table TESTT_ASCII_test1;

create table TESTT_UTF8_test1 (
pid int,
pcode char(11),
primary key(pid, pcode)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
load data local infile 'test.dat'
into table TESTT_UTF8_test1;
flush table TESTT_UTF8_test1;

The 'test.dat' is:
1 A0000000001
1 A0000000002
1 A0000000003
...
1 A0000100000

Then sizes of MyISAM files are:

# ls -l TESTT_*
-rw-rw---- 1 mysql mysql 1600000 Mar 21 11:06 TESTT_ASCII_test1.MYD
-rw-rw---- 1 mysql mysql 2350080 Mar 21 11:06 TESTT_ASCII_test1.MYI
-rw-rw---- 1 mysql mysql 8590 Mar 21 11:06 TESTT_ASCII_test1.frm
-rw-rw---- 1 mysql mysql 3800000 Mar 21 11:06 TESTT_UTF8_test1.MYD
-rw-rw---- 1 mysql mysql 852992 Mar 21 11:06 TESTT_UTF8_test1.MYI
-rw-rw---- 1 mysql mysql 8590 Mar 21 11:06 TESTT_UTF8_test1.frm

Why is the size of TESTT_ASCII_test1.MYI
larger than TESTT_UTF8_test1.MYI ,
and which is better performance?

In the case that the primary key has only pcode,
these files were the same size.

(MySQL version is 5.6.10, OS version is CentOS 6.3)

Thanks.
Kohama

Mysql group by returns wrong result (5 replies)

Pradeep kumar — Tue, 26 Mar 2013 06:25:43 +0000

I have an issue in one of mySql query. Issue is not reproducible on any of our local machines. I have a simple query

SELECT ID
FROM TABLE_NAME
WHERE ID IN (15920,15921)
GROUP BY ID

returns result –

ID
15920

Which is unexpected result since there is data for both the ids in database.

Using explain command returned the following result for this query

+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+
| id | select_type | table | type | possible_keys | Key | key_len | Ref | rows | Extra |
+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+
| 1 | SIMPLE | TABLE_NAME | range | CUST_SID_SRUN_INDX | CUST_SID_SRUN_INDX | 4 | | 1 | Using where; Using index for group-by |
+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+

For this issue I have tried following solutions -

• Forcing a derived table –

SELECT ID
FROM (SELECT ID
FROM TABLE_NAME
WHERE ID IN (15920,15921)) CUST
GROUP BY ID

• Using having clause instead of where clause

SELECT ID
FROM TABLE_NAME
GROUP BY ID
HAVING ID IN (15920,15921)

• Ignoring the index used in this table –

SELECT ID
FROM TABLE_NAME IGNORE INDEX (CUST_SID_SRUN_INDX)
WHERE ID IN (15920,15921)
GROUP BY ID

All the above queries return the expected result as follow :-

ID
15920
15921

I am trying to analyze the unexpected behavior of group by clause when indexes are used. Please let me know if I could try something else. FYI…The UAT box where the issue occurs is a linux machine with Mysql 5.1.30. The difference that we see is the version of Mysql. We are using Mysql 5.1.52 on our machines. The table which has this issue uses MyISAM databse engine.

Index definition for the table is

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
rm_st_ex_customer 1 CUST_SID_SRUN_INDX 1 SIMULATION_ID A 68 BTREE
rm_st_ex_customer 1 CUST_SID_SRUN_INDX 2 SIMULATION_RUN A 428 BTREE

Please let me know if any other input is required.

After creating a big amount of temporary tables I get this message: Can't create table 'table_name' (errno: 22) the binary log entry just before getting the error : DROP /!40005 TEMPORARY / TABLE IF EXISTS... (1 reply)

jani killer — Sun, 24 Feb 2013 01:01:07 +0000

After creating a big amount of temporary tables I get this message: Can't create table 'table_name' (errno: 22)

the binary log entry just before getting the error :

DROP /*!40005 TEMPORARY */ TABLE IF EXISTS...

Please reply fast i am waiting

MyISAM index issue (1 reply)

Wayne Clancy — Tue, 12 Feb 2013 16:08:16 +0000

Hi

I was wondering if anyone can point me in the right direction. I have a problem on 5.1.66 running on Centos 6.

Any MyISAM table which is indexed creates a *.MYI fil 1GB in size even if the index is only a few kb. Well when I say 1GB in size, that's the reported size using du or dk however ls reports the correct size.

At first I thought this might be a operating system / FS issue but when I optimized one of the affected tables the problem went away..until it was re-indexed.

Any help on this matter would be really appreciated as I've spent most of the day trying to figure this out.

Regards

Wayne

issue with myisam (1 reply)

miky heky — Sun, 10 Feb 2013 03:48:31 +0000

Hi,

Just got the below error in one of the slave .
I just googled it and changed the variable "myisam_data_pointer_size" from 4 to 6.
some how my alter was sucessful.
Can you please let me know whther my approch was right .. i am not sure on this variable.
will this cause any performance issue.
should i roll back it ?

130208 9:40:19 [ERROR] Slave: Error 'The table '#sql-19fc_3f8acc6' is full' on query. Default database: 'registry'. Query: 'alter table enom_log_2012_12_03 add responsedatatype char(1) not null after requestdata', Error_code: 1114
130208 9:40:19 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'pumpkin-bin.008662' position 411952695
Problem Description:-
Check for the Alerts in Errorlog for any errors and failures.Check if Mysql is up? if not verify if the process was Normal shutdown.
Advice:-

mysql> SHOW TABLE STATUS FROM registry LIKE 'enom_log_2012_12_03'\G
*************************** 1. row ***************************
Name: enom_log_2012_12_03
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 1786679
Avg_row_length: 2403
Data_length: 4293600396
Max_data_length: 4294967295
Index_length: 60999680
Data_free: 0
Auto_increment: 1786680
Create_time: 2013-02-06 15:19:07
Update_time: 2013-02-06 15:22:16
Check_time: 2013-02-06 15:26:35
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> show global variables like '%tmp%';
+----------------+-----------------------+
| Variable_name | Value |
+----------------+-----------------------+
| bdb_tmpdir | /usr/local/mysql/tmp/ |
| max_tmp_tables | 32 |
| tmp_table_size | 8388608 |
| tmpdir | /usr/local/mysql/tmp |
+----------------+-----------------------+

mysql> show global variables like '%myisam%';
+---------------------------------+---------------+
| Variable_name | Value |
+---------------------------------+---------------+
| myisam_data_pointer_size | 4 |--before changing it was 4 .. i changed it to 6
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 4194304 |
| myisam_stats_method | nulls_unequal |
+---------------------------------+---------------

LOCK replication safe? (1 reply)

Brad Proctor — Sat, 09 Feb 2013 18:55:29 +0000

We have master-slave replication using MyISAM tables. I'm wondering if a LOCK will block a SELECT on a slave.

I have a situation where only one can be active at any given time.

LOCK TABLE tbl WRITE;
UPDATE tbl SET status = 0 WHERE loc = 'A';
<---- (from slave) SELECT * FROM tbl WHERE loc = 'A' AND status = 1;
INSERT tbl (loc, status) VALUES ('A', 1);
UNLOCK TABLES;

This would normally block the SELECT until after the table was unlocked, but will this work if the SELECT is coming from a slave?

If not, is there another way to do this?

Thanks,
Brad

5.1 to 5.5 upgrade causes significant write slowdown (MyISAM + indexes) (23 replies)

Ben Wallach — Fri, 01 Feb 2013 21:37:30 +0000

5.1 to 5.5 upgrade causes significant write slowdown (MyISAM + indexes)

We just upgraded from 5.1 to 5.5. An insert script that used to take ~3 mins to insert ~200k rows into a MyISAM table with 3 indexes now takes ~8 mins! Removing the indexes solves the issue!?

Reads(selects) are fine (faster in 5.5 than 5.1)

Is something different about index behavior or could there be some oddball config parameter that may need to be tweaked?

Thanks in advance for any pointers!

System Info:
-----------
Fedora 17
Mysql 5.5.29-log
16GB RAM
MyISAM Key Buffer = 4GB
MyISAM index size = ~800 MB
MyISAM table size = 4.4 GB

INNODB Buffer Pool = 8GB
INNODB table+index size = ~12GB

Waiting for table metadata lock (2 replies)

Jon Ribbens — Tue, 08 Jan 2013 11:15:02 +0000

Our MySQL server (5.5.28 running on Ubuntu 12.04) keeps wedging a particular MyISAM table. From "SHOW PROCESSLIST" it appears that it is hanging on a "LOCK TABLES WRITE" statement in the state "Waiting for table metadata lock", and that no other commands are operating at the moment that it wedges.

Any suggestions as to how to resolve this gratefully received.

Concurrency probloem of MyIsam. (1 reply)

landtunes nomatter — Sat, 05 Jan 2013 21:23:57 +0000

I use mysql 5.5.15 to realize a data exchange queue bewteen two Process. I created a MyIsam table that only include two blob fields.
Processes A and B at the same time to operate the table,Process A insert record continuously,Process B get the first record and delete it continuously.
However, I found that inserted data by process A and obtained data by process B inconsistencies in the number.Such as process A insert 1000 records,but process B only obtained 900 records,and randomly lost some records.
The following is my my.cnf file:

# The MySQL server
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/var/data
port = 3306
socket = /usr/local/mysql/var/mysql.sock

skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 32M
concurrent_insert = 2
max_write_lock_count=1
low-priority-updates = 1
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
wait_timeout=28800
interactive_timeout=28800
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2

log-bin=mysql-bin

binlog_format=mixed

server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Please give me some advice, thank you!

Rebuilding Index (1 reply)

Charles Siu — Thu, 20 Dec 2012 20:48:00 +0000

Currently the performance of MySQL is low. After checking the structure of some tables via phpMyAdmin, I found that the cardinality of some indexes are strange. Sometime the cardinality is 0, and sometime the cardinality is simply the same as the number of rows. After I repair/optimize the tables, the cardinality return back to what I expect and the queries are returned faster than before.

I would like to know if there any way to identify the tables with such issue? Then I will do optimize table for those tables only.

Thanks in advance.

convert myisam to innodb - table status (3 replies)

Greg K. — Sun, 13 Jan 2013 06:00:11 +0000

hi

I'm going to convert a huge table (150GB) from myisam to innodb and wondering if during this operation the table can receive updates and inserts? Can it be done on-line??
This action will take on my HW serveral hours.
Please explain to me what happens to the converted table?

Help to optimize query (1 reply)

Daniel Morais — Sun, 02 Dec 2012 15:23:14 +0000

First of all, I'm sorry if this isn't the correct forum for this kind of thing, but since I'm using MyISAM, I'm posting here :)

I have a query which is taking more than 10 minutes to finish and I'm not sure what else can be done to optimize either the query or the database.

The query is:

SELECT 'View' Name, view.fk_id_affiliate_site ID, Count(*) Quantity, campaign.billing_type Billing_type, campaign.id_campaign Id_campaign,
                        campaign.percent_cut Percent_campaign
                        FROM `spd_log_view` view
                        INNER JOIN spd_unit unit ON ( view.fk_id_unit = unit.id_unit AND unit.fk_id_ad_format = 2 )
                        INNER JOIN spd_ad ad ON ( unit.fk_id_ad = ad.id_ad )
                        INNER JOIN spd_campaign campaign ON ( ad.fk_id_campaign = campaign.id_campaign )
                        INNER JOIN spd_affiliate_site site ON ( site.id_affiliate_site = view.fk_id_affiliate_site )
                        WHERE timestamp BETWEEN 1353898800.0000 AND 1353985200 GROUP BY view.fk_id_affiliate_site, campaign.id_campaign
                        UNION
                        SELECT 'Click' Name, click.fk_id_affiliate_site ID, Count(*) Quantity, campaign.cpc CPC, campaign.cpm CPM, campaign.billing_type Billing_type, campaign.id_campaign Id_campaign,
                        IF(site.percent_cut = 0,campaign.percent_cut ,site.percent_cut) Percent, site.percent_cut Percent_site, campaign.percent_cut Percent_campaign
                        FROM `spd_log_click` click
                        INNER JOIN spd_unit unit ON ( click.fk_id_unit = unit.id_unit AND unit.fk_id_ad_format = 2 )
                        INNER JOIN spd_ad ad ON ( unit.fk_id_ad = ad.id_ad )
                        INNER JOIN spd_campaign campaign ON ( ad.fk_id_campaign = campaign.id_campaign )
                        INNER JOIN spd_affiliate_site site ON ( site.id_affiliate_site = click.fk_id_affiliate_site )
                        WHERE timestamp BETWEEN 1353898800.0000 AND 1353985200 GROUP BY click.fk_id_affiliate_site, campaign.id_campaign

Explain output:

+----+--------------+------------+--------+----------------------------------------------+-----------+---------+-------------------------------------------+----------+----------------------------------------------+
| id | select_type  | table      | type   | possible_keys                                | key       | key_len | ref                                       | rows     | Extra                                        |
+----+--------------+------------+--------+----------------------------------------------+-----------+---------+-------------------------------------------+----------+----------------------------------------------+
|  1 | PRIMARY      | view       | range  | fk_id_affiliate_site,fk_id_unit,timestamp    | timestamp | 8       | NULL                                      | 32725996 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY      | unit       | eq_ref | PRIMARY,t_anuncio_t_pecas_fk,fk_id_ad_format | PRIMARY   | 4       | prod_front_end.view.fk_id_unit            |        1 | Using where                                  |
|  1 | PRIMARY      | ad         | eq_ref | PRIMARY,t_campanha_t_grupo_anuncio_fk        | PRIMARY   | 4       | prod_front_end.unit.fk_id_ad              |        1 |                                              |
|  1 | PRIMARY      | site       | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.view.fk_id_affiliate_site  |        1 |                                              |
|  1 | PRIMARY      | campaign   | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.ad.fk_id_campaign          |        1 |                                              |
|  2 | UNION        | click      | range  | fk_id_affiliate_site,fk_id_unit,timestamp    | timestamp | 8       | NULL                                      |    68398 | Using where; Using temporary; Using filesort |
|  2 | UNION        | unit       | eq_ref | PRIMARY,t_anuncio_t_pecas_fk,fk_id_ad_format | PRIMARY   | 4       | prod_front_end.click.fk_id_unit           |        1 | Using where                                  |
|  2 | UNION        | ad         | eq_ref | PRIMARY,t_campanha_t_grupo_anuncio_fk        | PRIMARY   | 4       | prod_front_end.unit.fk_id_ad              |        1 |                                              |
|  2 | UNION        | site       | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.click.fk_id_affiliate_site |        1 |                                              |
|  2 | UNION        | campaign   | eq_ref | PRIMARY                                      | PRIMARY   | 4       | prod_front_end.ad.fk_id_campaign          |        1 |                                              |
| NULL | UNION RESULT |  | ALL    | NULL                                         | NULL      | NULL    | NULL                                      |     NULL |                                              |
+----+--------------+------------+--------+----------------------------------------------+-----------+---------+-------------------------------------------+----------+----------------------------------------------+
11 rows in set (0.04 sec)

I'm an AWS RDS user, so I installed MySQL 5.5 with all the default variables, and changed only these two:

mysql> show variables LIKE '%table_size';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 67108864 |
| tmp_table_size | 67108864 |
+---------------------+----------+

Any help will be very much appreciated.

Thanks in advance.

Unknown MyISAM file (1 reply)

Tom Cleaveland — Wed, 21 Nov 2012 15:43:01 +0000

Noob question: I've been using MySQL 5.5.28 on 64-bit Windows 7 for a few months. There's a file on my system that I can't identify called "localhost.MyISAM". It's currently over 300 GB.

Where might I find information on this file, and about how to manage it? It's occupying about 60% of the drive.

Thanks for any info,
Tom

Enable Keys use keycache with "errorno 0" - 800Million Rows in one partition - 5 index (3 replies)

Emmanuel Vincent — Wed, 07 Nov 2012 09:17:25 +0000

Hi all,

I have the following issue

I use mySQL version 5.5.14 , Linux Redhat 64 bits

I Would like to use "Repair by sorting" instead of "repair by keycache"
but I can't success

When I perform "Alter table MyTable enable keys" In log I always see this:
[Warning] Warning: Enabling keys got errno 0 on MyBase.Mytable, retrying
And then the repair is done with KeyCache

my.cnf is like this:
- myisam-sort-buffer-size=16G => I've tried also with 32G/64G
- key_buffer_size=16G => I've tried also with 32G
- myisam-max-sort-file-size=90G ==> file system has 100G

I have tried "myisamchk -r" and it works well "with sort" but it isn't what I want to use moreover it is not supported for partition.

I want to use "Alter Table..."

After repair is finish
Partition Table File size is on disk : 25G
Partition Index File size is on disk :
- 8G "with sort using myisamchk -r"
- 16G with keycache using "Alter table..."

(for information i have also tried with thread and it works well but it is beta code.)

same issue whether I do "Repair table" I can see "Keycache" but I haven't any error in logs

Finally I have 32 hours using "Repair Keycache" and 1 hour using "Repair by Sort"

Any help would be great

Index Keys of the table (4 replies)

fei lee — Mon, 05 Nov 2012 02:54:15 +0000

Dear Sir/Madam,

I have table structure as following:

CREATE TABLE IF NOT EXISTS `contacts` (
`ContactID` varchar(30) NOT NULL,
`Name` varchar(30) NOT NULL,
`Gender` char(1) NOT NULL,
`Phone` varchar(16) NOT NULL,
`Email` varchar(50) NOT NULL,
`Address` varchar(100) NOT NULL,
`DOB` varchar(8) NOT NULL
PRIMARY KEY (`ContactID`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Users can search the contact with following fields:
- Name
- Gender
- Phone
- Email
- DOB

Possible SELECT queries are:
SELECT * FROM contacts WHERE Name = ?;
SELECT * FROM contacts WHERE Phone = ?;
SELECT * FROM contacts WHERE Name = ? AND Gender = ?;
SELECT * FROM contacts WHERE Phone = ? AND Email = ?;
SELECT * FROM contacts WHERE Name = ? AND Gender = ? AND Phone = ?;

I wonder whether I should create an index key with all searching fields, or create index keys one by one.

As I understand a 'grouped' index keys could give higher performance rather than individual index keys.
However, since the users may search different fields each time, how should I manage my index keys for good/high performance?

Should I create the KEY as following?
KEY(Name, Gender, Phone, Email, DOB)

OR following will be better?
KEY1(Name)
KEY2(Gender)
KEY3(Phone)
KEY4(Email)
KEY5(DOB)

Your comments will be highly appreciated!

order by rand() Query takes too long (writes to disk) (1 reply)

Bernd Meier — Tue, 30 Oct 2012 18:22:28 +0000

HI

I got a MYISAM table in my database with about 7 million rows.

table has index,url,external_link_count fields.

Now I want a seachquery to get one url of each domain.Each query should return different urls than the last query.

so I do:
SELECT x.* FROM (SELECT * FROM table WHERE external_link_count < 50 order by rand()) x GROUP BY SUBSTRING_INDEX(url,'/',3)

Now my problem is:
1) The query locks the database and the same query from other tools can not access the datbase -> huge delay time
All other tools has to wait for the current query to complete.

2) The query writes a "temp" table to disk -> huge delay time

Question:
Is it possible to do a smarter and faster query to solve this problem, or is there any idea to tweak the performance?

The running tools execute this query 4-8 times and hours in total.

System:
I5 Core 2,7Ghz
16 GB Ram
120 GB SSD Disk
Windows 7 Enterprise 64Bit

Thank you very much in advance

Working with short words in full text search (2 replies)

Thato Mantai — Tue, 06 Nov 2012 21:00:38 +0000

I noticed this limitation in MySQL. "The default minimum length of words that are found by full-text searches is four characters." How does one circumvent this limitation. Can I modify the default? If so, how?

Thanks
Thato Mantai

Help understanding the relationship between threads and opened tables for MyISAM (1 reply)

Michael Finch — Thu, 11 Oct 2012 02:02:08 +0000

Hey,

We've been having trouble understanding the relationship between threads and opened tables for MyISAM for a while now, despite reading all of the documentation. Quoting the mysql docs, "To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session". Following this logic, it would seem that if you create 5 different connections and they all perform a sql_no_cache count(*) on a table, 5 copies of that table will be opened. However, we are only seeing this behavior when the queries are made SIMULTANEOUSLY (5 copies of the table can be seen by looking at file descriptors, and we see 5 tables when we run >show global status like "Opened_tables";). When the query is made sequentially on each connection, though, all connections appear to share the file descriptors and >show global status like "Opened_tables"; only equals 1.

The specific problem that we are having is that many of our tables are heavily partitioned, and we are running into issues around too many file descriptors being opened. For example, we often see tables with 150 partitions opened 20 times (20x150 copies of MYD file descriptors opened, 1x150 copy of MYIs). We allow up to 300 simultaneous connections, but there is no point in time where those tables are getting queried 20 times simultaneously. Anyway we were thinking that, per what the docs say, maybe 20 different threads were each holding onto a copy of the tables, but our basic testing has shown that the threads will in fact share the MyISAM resources if they are not being used at the same time.

Any help would be greatly appreciated. Thanks!

MySQL Full Text Search not returing all records (2 replies)

Brett Atkin — Tue, 18 Sep 2012 13:39:15 +0000

First time implementing Full Text Search...

I've run the necessary ALTER TABLE SQL to enable FULLTEXT on the applicable tables/fields.

On the following beta site: http://wtc.betaforming.com/

If I do a search for "leadership", I get normal results except for the Events section at the bottom. I have Events with that word in the event title and throughout the description copy (http://wtc.betaforming.com/events/event/?event_id=10039).

If I do a search for "communications", I get results in the Events section, which makes me think I have everything configured correctly.

I'm using the following basic code for testing purposes:

SELECT *
FROM tblevents
WHERE MATCH(event_title, event_desc_long, event_desc_short, event_tab_one_title, event_tab_one_text, event_tab_two_title, event_tab_two_text, event_tab_three_title, event_tab_three_text, event_tab_four_title, event_tab_four_text) AGAINST ('$site_search_term')

This is the same code I'm using to search Products and Articles (changing the necessary FROM and WHERE information).

Not sure what is happening (since it works for some phrases) or where to start looking in my db to see what is wrong.

Thanks

Brett

Add multiple interrior ring to mysql spatial extention (1 reply)

Hendra Darwintha — Thu, 20 Sep 2012 08:05:01 +0000

Hi, this is first time for me to use MySql spatial extension (MyIsam)

I'm currently convert all polygon in ESRI shapefile format (.SHP)
into MySql geometry column table

base on documentation:
A Polygon with one exterior ring and one interior ring:
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))

I'm having problem where the polygon have 3 exterior ring and 2 interior ring
how can i do that with Insert Query

Thanks

query scanning all rows (1 reply)

jay oberoi — Sat, 15 Sep 2012 18:44:39 +0000

Guys trying to wrap my head around mysql query on why it is scanning all rows in table

I have 2 tables topic_entry and topic_user

CREATE TABLE `topic_entry` (
`entry_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` bigint(20) unsigned NOT NULL,
`entry_created` datetime NOT NULL,
`entry_object` text,
`level` tinyint(4) NOT NULL DEFAULT '3',
PRIMARY KEY (`entry_id`),
KEY `entry_created` (`entry_created`),
KEY `level` (`level`),
KEY `topic_id_2` (`topic_id`,`entry_id`)
) ENGINE=MyISAM;

CREATE TABLE `topic_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` bigint(20) unsigned NOT NULL,
`user_id` varchar(100) NOT NULL,
`private` enum('y','n') DEFAULT NULL,
`freq` enum('a','d','w') DEFAULT NULL,
`topic_id_freq` varchar(10) DEFAULT NULL,
`casematch` enum('0','1') DEFAULT '0',
`textmatch` enum('0','1') DEFAULT '0',
`topic_name_case` varchar(100) DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `topic_id_user_id` (`topic_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `topic_id_freq` (`topic_id_freq`)
) ENGINE=MyISAM;
This is the query that i am trying to run

explain
select te.topic_id,te.entry_id
from topic_entry te
WHERE te.topic_id in (select topic_id
from topic_user where user_id ='xyz')
AND te.level=4

ORDER BY te.entry_id DESC
LIMIT 5;

The explain output shows that it is scanning all rows
| 1 | PRIMARY| te | range | PRIMARY,level | PRIMARY | 8 | NULL| **722978** | Using where |
| 2 | DEPENDENT SUBQUERY | topic_user | unique_subquery | topic_id_user_id,user_id | topic_id_user_id | 310 | func,const | 1 | Using index; Using where |

If i remove order by clause from query it work perfectly
Any pointers would be appreciated

fulltext boolean search not returning a particular row (3 replies)

Sascha Monteiro — Mon, 03 Sep 2012 00:01:34 +0000

Hi,
I have a record containing "Do You Still Think of Me" (the table has about 2700 records),
and the following query does not return the record;
SELECT * FROM productions
WHERE MATCH (title) AGAINST ('+Do +You +Still +Think +of +Me' IN BOOLEAN MODE)
(neither with the + removed, or less keywords)
The rest of the table seems to be returned okay on their keywords, just not this record.
The ft min word length is 4, but should still return if I query on +Still +Think
MySql version is 5.0 (this is hosted, can't upgrade..)
there is a fulltext index on title, and the engine is MyIsam
I repaired and optimized the table, dropped and recreated the fulltext index, no luck :-(

Any pointers?

Almost whole table in single index: is there a better way? (11 replies)

Andy Deakin — Wed, 29 Aug 2012 05:20:57 +0000

I have have a MyISAM table which collects live data (e.g. wind speed, temperature) and other data from remote data loggers. Each data logger has an unique RemoteID, and each type of data (e.g. wind speed) has a specific DataID. The time SensorTime is stored as a unix timestamp. To improve performance, the table is partitioned by month, using the unix timestamps. There are around 250 million rows added per month, and the data is kept for 12 months by dropping the partition which is 12 months old.
Value is a char(8) because sometimes the data might not be numeric, and I wanted a fixed row length.

A typical query might try to plot a weeks worth of data for a single sensor, and looks like:
SELECT SensorTime,Value FROM RemoteStatuses WHERE RemoteID = X AND DataID = Y AND SensorTime BETWEEN A AND B

Data is regularly inserted in batches of around 100 in a single "INSERT IGNORE" statement.

CREATE TABLE `RemoteStatuses` (
`RemoteID` int(11) NOT NULL,
`SensorTime` int(11) NOT NULL,
`DataID` smallint(6) NOT NULL,
`Value` char(8) NOT NULL,
PRIMARY KEY (`RemoteID`,`DataID`,`SensorTime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (SensorTime)
(PARTITION p201208 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-08-01 00:00:00') ),
PARTITION p201209 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-09-01 00:00:00') ),
PARTITION p201210 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-10-01 00:00:00') ),
PARTITION p201211 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-11-01 00:00:00') ),
PARTITION p201212 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-12-01 00:00:00') ),
PARTITION pend VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

Indexing three out of four of the columns seems a bit much, and results in large index files (MYI and MYD are comparable in size)
Can any clever person out there think of a better way of storing and indexing all three columns?

5% of spare memory key buffer big enough? (2 replies)

Josh Irish — Sun, 19 Aug 2012 19:41:23 +0000

Hi All,

I'm looking into optimizing my companies mysql server, and one of the first places I've looked at is the key buffer size. At the moment it's set to 400MB, and according to the server there is generally about 10GB of memory free, so that's less than 5%, and from what I read it's generally a good idea to have it between 25 and 50%

It's a pretty large database, 200GB+, and some of the indexes on single tables are over 1GB, so I expected the low key buffer size to adversely affect performance, but I checked the stats for them and they don't seem that bad:

| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 484 |
| Key_blocks_used | 319666 |
| Key_read_requests | 399516496499 |
| Key_reads | 223702306 |
| Key_write_requests | 4692155028 |
| Key_writes | 1711169289

(I'm not overly familiar with these stats so perhaps I'm interpreting them incorrectly)

I realise the ideal size is largely dependant on the data and usage of the database, which is extremely varied, but would there still likely be some benefit to increasing the size?

Also my our system administrator says mysql would need to be restarted after changing key_buffer_size, but I've not seen anything that states that, is it the case? (Server version is 5.5.1)

Thanks!

Two instances better - or mix MyISAM and InnoDB? (4 replies)

zenia — Sun, 19 Aug 2012 04:15:50 +0000

I may have submitted this question to the wrong forum. I'll try here.

We use MySQL to support a large OLAP type application with one very large table that is only updated once a month. By large, I mean 1.5 billion rows. We use MyISAM for this table for performance reasons, and also because we use merge tables to facilitate the monthly updates (dropping the oldest merge table, and adding a new one).

We are developing a new application that is highly relational and makes heavy use of real-time user-driven transactions with joins and commits. We will be using Innodb tables for this functionality.

We have a dedicated db server that currently runs one instance of MySQL with the MyISAM table format. To support the new transaction-driven functionality under development is it better to continue with one instance of MySQL and mix the two table types, or would it be better to run two instances of MySQL, with different ports and data directories?

Thank you for any insight you might have...

-Jim Gallagher

Error "Duplicate entry for key 1", but there don't seem to be any duplicates... (1 reply)

Kenn Baker — Fri, 03 Aug 2012 05:06:52 +0000

I am performing a sync process on some data in a mysql database (The mysql server version is 5.0.91-log and it is running on a Linux box).

I am receiving an error like "Duplicate entry '26552' for key 1" every time a certain query sequence is executed as part of a sync process I have set up.

The entry number is gradually increasing each time the query fails as per "Error in query: Duplicate entry '25065' for key 1" then "Error in query: Duplicate entry '25337' for key 1", then "Error in query: Duplicate entry '25759' for key 1" ... , but other than that, the error is the same.

The error is returned as a result of running the query, "INSERT INTO `mydatabase`.mytable_just_before_umd_sync SELECT * FROM `mydatabase`.mytable"

I run the process via cron on an hourly basis.

In order to verfify the sync process, I create some temporary copies of table/s that can be modified by the sync process.

I do this using some php, as below (executeQuery(...) is basically mysql_query($query) with some error checking and logging):

$tableName = "mytable_just_before_umd_sync";
$query = "DROP TABLE IF EXISTS $tableName";
$result = executeQuery($query, $tableName, __FUNCTION__, __LINE__);

$query = "CREATE TABLE $tableName LIKE $table";
$result = executeQuery($query, $tableName, __FUNCTION__, __LINE__);

$query = "INSERT INTO $tableName SELECT * FROM $table";
$result = executeQuery($query, $tableName, __FUNCTION__, __LINE__);

mytable is as follows:

CREATE TABLE `mytable` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`user_id` bigint(20) unsigned NOT NULL,
`school_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED AUTO_INCREMENT=26958 ;

I've checked, and there are no duplicates in mytable (couldn't really be anyhow as id is primary).

Is key 1 refering to mytable_just_before_umd_sync.id?

Would this be some kind of race issue (i.e. is the DROP TABLE still operating and then maybe the INSERT INTO operation catches up and hence the duplicate. I don't know how DROP TABLE works? Does it complete before returning?)?

Should I put something like "LOCK TABLES `mytable` WRITE;" and "UNLOCK TABLES;" around the "INSERT INTO $tableName SELECT * FROM $table;" command?

I've used mysql for some time, but this is a bit above my level.

Anyone have any ideas about what might be causing this issue?

I've googled and looked into the issue, but am at a bit of a loss really.

any help appreciated

Kenn

A Question on MyISAM table locking on joins. (1 reply)

John Snow — Fri, 03 Aug 2012 06:38:28 +0000

Recently we had a crash that inadvertantly switched our DB over from Innodb to MyISAM. I just want to confirm a behavior I was seeing in the slow log.

There were certain queries coming through that did multiple joins across many tables where the inner most table was a huge regex search on a varchar field on a table with nearly a billion records in it that was forcing a full table scan. This query if ran multiple times would cause locks to occur.

My question is that since this is an abysmally slow query, does that query also lock every table in the join that is a part of the query, or just the table that it's currently searching on?

I was noticing huge performance issues caused by the accidental switch over, and I was wondering if possibly cascading table locks were occuring from the query mentioned above that was locking out othe tables in the system that other users might have been using?

Thanks for the help!

Improve performance of a query with join and order by (1 reply)

Mario Rossi — Tue, 17 Jul 2012 05:08:42 +0000

Hi all, i have the following situation:

First table is table1(field1,field2,field3,.......,field11,field12) and second table is table2(a, b, c)

a is foreign key for field1 which is an integer.

field12 is a timestamp and it has an index on it.

table1 has nearly 2000000 records and table2 has nearly 56000000 records and both tables are MyISAM.

I have to perform the following query:

SELECT t1.*
FROM table1 t1
JOIN table2 t2 ON t2.a = t1.field1
AND t2.b = '83'
AND t2.c = '66'
WHERE t1.field3 IN ( 6 )
AND t1.field4 = '0'
ORDER BY t1.field12
LIMIT 5000

The problem is that it takes nearly 12 minutes to execute and i don't get why.
Expecially i don't get why the index can't help inside the ORDER BY.

I tried to execute it with explain and i can see that it's not using the index on field12.

Can someone tell me what am i missing?

Thanks in advance for the help.

Es posible recuperar datos de una tabla (no replies)

Mauricio Zambrana — Thu, 12 Jul 2012 19:30:32 +0000

se puede recuperar datos los datos de una tabla myisam que fueron eliminador por delete * from tabla

gracias

How to index a 1.5 billion rows (6 replies)

Vladimir Pivovar — Sun, 29 Jul 2012 10:42:01 +0000

Hi!
How to index a 1.5 billion rows
quickly through the fields of type VARCHAR(32)

My first encounter with indexation.
What should be done before indexing. What to do configuration
Is it possible to calculate the time of indexing?

P.S. OS-Windows XP
MySQL-5.5.25
CPU Athlon 2*2GHZ
RAM DDR2-800 2GB
HDD Hitachi 1TB 8MB