All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
1answer
29 views

show extended index equivalent for MySQL 5.7

If you look at this documentation for show index in version 8.0, it says SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] The ...
0
votes
0answers
63 views

index corrupt error while trying to index a large table

I am trying to index a large table, approximately 12 million rows and 33GB in size. There is currently no indexing on the table. When I am trying to index one of the columns in a table, I get the ...
-1
votes
1answer
139 views

MariaDB: Duplicate entry for key on OPTIMIZE with no duplications

There is a table named contracts with following structure: MariaDB [db_name]> DESCRIBE `contracts`; +-----------------------------+--------------+------+-----+---------+----------------+ | Field ...
8
votes
2answers
329 views

Covering index used despite missing column

I have the following query, using MariaDB 10 / InnoDB: SELECT id, sender_id, receiver_id, thread_id, date_created, content FROM user_message WHERE thread_id = 12345 AND placeholder = FALSE ORDER ...
0
votes
0answers
6 views

InnoDB clustered index performance when using random values as primary key

By default, my primary keys of InnoDB storage engines are auto increment integers. In effort, to hide the number of rows in the database, application code implements some random generator for the ...
3
votes
1answer
30 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
0
votes
1answer
1k views

Subtract values in same column from 2 different dates in same Mysql table

I have a MySql table like: Item Date Likes Book 2012-05-20 402 Book 2012-05-21 432 Book 2012-05-22 450 Pen 2012-05-20 20 Pen 2012-05-...
0
votes
1answer
36 views

Planning and Optimizing the table structure InnoDB MySql

I am planning to have an InnoDB MySql table with tens of millions of rows. Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
4
votes
2answers
73 views

Performance issues with groupwise MAX in JOIN clause

Problem I have some assets in my application which are updated from time to time in an asynchronous fashion. The example I'm gonna use here is Vehicles. There are two tables: Vehicles: holds ...
5
votes
4answers
626 views

Which columns should be indexed when all may be used in different search queries?

Background I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
0
votes
2answers
375 views

Find MySQL InnoDB index corruption

I have large MySQL InnoDB table on my localhost. In this table primary key is id, unique index is set on fields a, b, c. When I run query: SELECT * FROM mytable WHERE a = 1 AND b = 2 AND c = 3; the ...
2
votes
2answers
1k views

Strange deadlock with multi-column index with innodb on MySQL

I'm seeing some odd (to me) behavior in MySQL. Let's start with the table that I'll be talking about. CREATE TABLE `active_foo` ( `active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT, `c_id` ...
0
votes
1answer
92 views

What are the pros and cons of adding secondary columns to a table's primary key?

In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table. I have a photos table. Queries on it come in many different ...
0
votes
5answers
541 views

Slow queries on indexed columns (large datasets)

I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete. product_base (1.5GB, 26 million rows) CREATE TABLE `product_base` ( `EAN13` varchar(13) NOT NULL DEFAULT '', ...
1
vote
1answer
68 views

Setting up indexes to support a flexible API search endpoint

I have a MySQL 5.7 table that holds activities that users can search through. It will end up holding less than 100,000 rows and inserts will be less than 50/day. This activities table has a structure ...
2
votes
1answer
622 views

Why scanning an index backwards is slower?

Empirical tests show that a query like this on an InnoDB table: SELECT indexed_column FROM tab ORDER BY indexes_column ASC; is faster than its counterpart with ORDER BY ... DESC. Why is this the ...
1
vote
1answer
66 views

How Mysql Query Plan is generated? [closed]

Mysql before executing a query with multiple joins. It creates multiple query plan and find the lowest cost plan to execute the query(approx). My questions are How the query plan is generated on ...
0
votes
1answer
717 views

Wrong query execution plan for large tables

Problem: We are experiencing a problem with large tables (+40M rows) having frequently updated their indexed columns with low selectivity. It seems that when the column is often updated/deleted after ...
3
votes
4answers
1k views

Read performance when indexing one column vs indexing multiple columns

(MySQL, InnoDB) I have a table that has columns A, B and C. That table will have a lot of rows, let's say a million rows. I have a query that will be executed very often and will search the table by ...
0
votes
1answer
116 views

Innodb switch innodb_file_per_table to OFF

I want to switch off the inno variable: innodb_file_per_table I found that this affects only new tables, how to apply this change also to old tables? I tried: Alter table (ALTER TABLE xxx ENGINES=...
0
votes
1answer
39 views

InnoDB: Is a covering index in the form of (PK, col1, col2, col3) redundant?

Table structure for config: property (primary key) value description Query: SELECT property, value FROM config If I put a covering index on (property, value) the optimizer still chooses the ...
0
votes
3answers
197 views

Control InnoDB buffer pool allocation in MySQL (5.7+)

I have some large tables which causes to fill the InnoDB buffer pool and removing other data. Is there any way how can I control the InnoDB buffer allocation in one shared database? Like: a) Limit ...
0
votes
1answer
386 views

mysql ibd file in file system

I am in searching of where mysql stored its indexes for innodb engine.I found it stores in ibd file but where I will found those files on system.Can anyone help please?
2
votes
1answer
3k views

Does innodb_online_alter_log_max_size took memory from ram or Hard Disk

I got an Mysql (5.7) error while altering huge table (400 G) for adding an index: ERROR 1799 (HY000): Creating index 'FTS_DOC_ID_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of ...
1
vote
0answers
81 views

Should I add index when indexes are greater than RAM in mysql

Data_length + index_length are greater than innodb_buffer_size, and I have a huge table where the select took longer than 5 minutes. Should I resolve this issue by adding another index on a column, or ...
1
vote
2answers
1k views

Is two separate index equals to a composite index?

For example, this is a sample table id int PK, pid int, cid int, deleted int, createdAt timestamp, UNIQUE KEY pid_cid(pid, cid) used WHERE clauses are where pid=$pid and cid=$cid [and deleted = 1]; ...
6
votes
2answers
2k views

MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status?

Missing support for AUTO_INCREMENT columns in multiple-column index for InnoDB tables is a widely known limitation, as is the trigger workaround for when it is needed (see e.g this post). Yesterday, ...
0
votes
1answer
26 views

Can an InnoDB page contain data from different indexes?

(In this question, I assume that the data of a table is acťually an index (PRIMARY), so I don't differentiate between table data and column indexes) I've compacted an "old" InnoDB table (via null ...
6
votes
2answers
5k views

Autoincrement vs composite primary key for a big table on innodb

I have a had a quite lengthy discussion with Rick James on this this we came out with idea of having composite key to replace the autoincrement pk where the int is limited close 2 billion. My table ...
2
votes
1answer
335 views

cardinality & column order in compound index (MySQL/InnoDB)

I know the column order in a compound index matters if you want to query by a subset of the indexed-columns, but if you're query specifies values for all the indexed columns, is there any performance ...
1
vote
1answer
2k views

How to optimize a Full Text Search

So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause. Doing this search slows the query down by about 300ms and since the query is run a few ...
0
votes
1answer
76 views

Frequent Corrupt Indexes in MySQL 5.6. Is this a MySQL bug?

I´m having problems with mysql indexes with INNODB getting corrupted. Initially I solved it by rebuilding the tables but now the tables are to large to rebuild, and i´m tired of mysql not showing ...
3
votes
0answers
148 views

Fast index creation issue

One thing that I can't explain is creating secondary index that doesn't trigger fast index creation as it should. I noticed that while creating secondary index on larger table, it gets actually ...
1
vote
2answers
145 views

What is the most convenient indexing strategy for two foreign key constraints?

Which of the two following options is more convenient for indexing the relevant foreign keys? Option A: Create Table table3( t3_id int not null auto_increment, t1_id int not null, t2_id int ...
2
votes
2answers
64 views

How do I build index for MySQL table that inserts a lot and queries only most recent data

I have a MySQL database table using InnoDB and looks like this: OrgID (INTEGER) MachID (INTEGER) Date (DATETIME) IdleTime (INTEGER) I do two operations on this table: Insert a new record, with Org ...
2
votes
1answer
2k views

UPDATE does not use INDEX, but SELECT does

I'm actually struggling understanding the problem here. I read everywhere saying that UPDATE benefits from indexes on the WHERE clause. However, this query, UPDATE `documents` SET `read`="1" WHERE `...
2
votes
2answers
2k views

What are the causes of InnoDB Index Corruption?

I'm running into issues where many of my database servers are experiencing repeat index corruption. The index corruption across several physical hosts, and many different tables. Whenever I restore a ...
1
vote
3answers
31 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
1
vote
0answers
84 views

How similar is using a secondary index in InnoDB to using a second table joining 'into' the PK?

Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like ...
3
votes
1answer
536 views

Query with Indexes slower than without Indexes

I'm using MySQL 5.6 and my storage engine is InnoDB. I have a table with 1 million rows containing with the columns: ID (primary key) FirstName LastName foreign_key_id (foreign key, NOT NULL) ...
2
votes
1answer
423 views

In [Percona] MySQL, is the Adaptive Hash Index included in the Total memory allocated count?

I'm debugging a memory problem with MySQL 5.6 (I'm using the Percona distribution), but I can't figure out, searching around the web, if the Adaptive Hash Index amount is part of the Total memory ...
1
vote
1answer
96 views

InnoDB slow query (few derived and dependent queries)

I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins). I need to get all 0 values from those tables in the last WHERE ...
0
votes
1answer
711 views

Preventing MySQL index corruption

I'm having a recurring issue with a MySQL database where one index in particular has been getting corrupted. Originally I saw a corruption event about every 2-3 weeks and this week I've seen it happen ...
1
vote
1answer
121 views

If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

We have an issue with MySQL InnoDB performance. We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts ...
5
votes
2answers
494 views

Performance for MySQL InnoDB B+Tree index with many duplicate values

I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking ...
1
vote
1answer
384 views

Innodb full table Scan

mytable is an InnoDB table with (a, b) as the primary key. Does this query check all table? Select * from mytable where ( b='2' and c='3') I want to find out if 'cluster' structure helps ...
1
vote
2answers
80 views

Should I normalise this database?

I have two database designs thought of for storing messages(instant messages) between users. It will be a group chat system. I have two table designs in mind. One is this: CREATE TABLE `...
2
votes
2answers
3k views

slow query on mysql innodb table with 2 million rows

I have a table with about 2 million rows (growing at about 1 million every 3 months) and am having trouble with slow queries. I've tried changing my query and indexes (ensuring left most order) to no ...
2
votes
3answers
1k views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
1
vote
2answers
107 views

How to determine the best index on a table with 3 nullable columns?

I am examining a users table similar to this: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password` ...