0
votes
0answers
29 views

Why isn't this index helping my InnoDB MySQL query?

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query: EXPLAIN SELECT * FROM ...
0
votes
2answers
34 views

How to resolve: Specified key was too long; max key length is 767 bytes [duplicate]

Our server is creating a lot of tables and one of them is the Lock_ table. We are using MySQL 5.5 on Percona. Also we are using utf8_general_ci and utf8. It throws the following error when creating ...
2
votes
1answer
14 views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (suprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
0
votes
0answers
21 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
2
votes
1answer
47 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
1
vote
0answers
35 views

Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

I have a very large table (15 billion rows) with a structure like this: CREATE TABLE `textlines` ( `PGUID` binary(16) DEFAULT NULL, `StatID` int(11) DEFAULT NULL, `Time` datetime(3) DEFAULT ...
0
votes
1answer
24 views

MySQL FullText search on string shorter than 3 chars returns no rows

I have a MySQL table with some addresses in it. Let's say I have the following 2 rows: "10 Fake Street" "101 Fake Street" I'm trying to use fulltext search with MATCH() AGAINST(). I have set ...
0
votes
0answers
12 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
1
vote
1answer
31 views

Indexing the difference between two columns in MySQL

Is there a way to optimize/index a query that sorts the difference between two columns. I have a modest sized table of 5 million rows that I'm trying to do the following: SELECT pk_column, column_x, ...
0
votes
0answers
33 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
1
vote
0answers
20 views

Why do these two queries (which I think are equivalent) rely on different keys?

We have a query that we don't like, since it ends up doing a full table scan. EXPLAIN SELECT count(*) AS c, location.name FROM study LEFT JOIN location ON study.location_id = location.id ...
0
votes
1answer
27 views

select where index + other field

Learning indexing I have made an index, consisting of usergroupid and birthdate I understand that queries will then benefit from indexing when searching usergroupid and/or birthdate But what ...
3
votes
2answers
39 views

Why is MySQL ignoring my indexes?

I have a query select distinct id from patient where company_id in (1) and (name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%') running on a table that looks like this ...
0
votes
1answer
103 views

MySQL 5.1 -> 5.6 problem, select distinct order by limit query stops using index

I'm testing a MySQL 5.1 -> 5.6 upgrade, and I've run into an issue. I have a query that hits a ~30 million row table, selecting distinct IDs with an order by/limit restriction. Sample query is ...
0
votes
1answer
82 views

MySQL - Indexes deleted, reduced column size but table size increases

Here is the original table definition which contains 107547934 (~107.5M) rows CREATE TABLE `twitter_tweets` ( `id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ...
0
votes
0answers
38 views

Index on fields with a function

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
-1
votes
4answers
86 views

Index on 'GROUP BY' with functions

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
2
votes
2answers
70 views

I need help optimizing a not that complicated query that returns unexpedcted performance problems

I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the ...
0
votes
0answers
32 views

Mysql table with only two columns, nothing unique: Will an index increase search performance?

If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search ...
0
votes
1answer
34 views

Varchar indexing

I am very rusty with MySQL and I'd like some advice. I have several software tools that are deployed throughout the company and I am writing a DLL to keep track of the usage, errors and stuff of some ...
0
votes
0answers
21 views

Creating a table for indexing another table

We have a table with 350m rows and 12 columns, and around 1m rows adding everyday. We have crappy hardware and we cannot add an index on one of the 12 columns that we really really need to. (severe ...
0
votes
1answer
30 views

Optimizing a query so MySQL will use the indexes for the joins

Some context: migrating some data from a 3rd party cloud MongoDB platform service into MySQL. Wrote a query but it's very slow. Here's the query: SELECT Table1.*, (long list of fields for each ...
1
vote
3answers
331 views

What's the most efficient UUID column type

For storing a 128 bits UUID there are multiple storage options: a byte[16] column two bigint/long(64 bits) columns a CHAR(36) column - 32 hex digits + 4 dashes. a UUID database specific column, if ...
0
votes
2answers
61 views

Should I create a multi-column UNIQUE index?

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits): CREATE TABLE `Visits` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
2
votes
1answer
32 views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
2
votes
1answer
317 views

MySQL Index creation failing on table is full

UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space. Original Q: I'm trying to add an index to an InnoDB table, and ...
1
vote
1answer
41 views

Can't figure out this “trick” to optimize MySQL query

Here's an excerpt of this article: SELECT id, name, address, phone FROM customers ORDER BY name LIMIT 10 OFFSET 990; MySQL is first scanning an index then retrieving rows in the table by ...
1
vote
1answer
25 views

is there any need to index b again as it is already a under primary

Is there any need to index b again as it is already a under primary- if primary(a,b) and in query we have where b=' '; Is there any need to index(b) again individually because while indexing it ...
1
vote
1answer
59 views

MySQL from table to table data transfer

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create ...
1
vote
2answers
59 views

how to use an index on a mysql query with a where clause and a order by

I have a table CREATE TABLE activity ( id INT(10) UNSIGNED AUTO_INCREMENT, subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0', action VARCHAR(250) NOT NULL DEFAULT '', ip INT(10) UNSIGNED NOT ...
0
votes
1answer
44 views

How to index for multiple inequality WHERE search conditions?

I am thinking on the problem, if there is a multiple columns on which search must work as fast as possible. For example, see the table TEST(COL_A, COL_B) with a (tree) index TEST_IDX(COL_A, COL_B). ...
0
votes
1answer
189 views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
0
votes
1answer
58 views

MySQL is using wrong index

This is my table: - user_id INT (foreign key on `user` with the reference `id`) - question_id INT (foreign key on `question` with the reference `id`) - option_id INT (foreign key on `option` with the ...
0
votes
2answers
42 views

Why search by date field requires engine to touch all records?

Why search by date field requires engine to touch all records? Is there any way to avoid it? Following query EXPLAIN select * FROM playdays where realdate>=Date('2010,01,01') ; shows that ...
2
votes
1answer
258 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
1
vote
1answer
62 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
3
votes
4answers
1k views

How to check for existence of an INDEX on a column independent of used SQL database

Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used? For MySQL one could for instance check for the existance ...
2
votes
2answers
28 views

Can creating indexes colide with future database upgrades?

I have a mysql Database with very slow querys. I am using the slow query log to monitor those. This is a database created by a third party software that we're using. Their developers didn't do any ...
1
vote
0answers
26 views

Why is this MySQL query performing slowly despite correct indexing? [duplicate]

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
2
votes
1answer
75 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
0
votes
1answer
41 views

Can't force mysql to use index

There are many similar questions about force index but mine is something different because i successfully use index at local dev server but can't on production server. I have a table with more than ...
1
vote
0answers
81 views

What is a loose index scan?

I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan" What exactly does this mean? Is it related to the underlying or the implementation of the ...
0
votes
1answer
53 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
1
vote
2answers
164 views

Why is MySQL not using the index with the higher cardinality?

I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
2
votes
1answer
52 views

What happens when CREATE INDEX gets interrupted (manually or accidentally)?

It takes hours or even days to create an index for a table of millions of rows. What happens to the database / data when CREATE INDEX gets interrupted? Such as when I manually used Ctrl-C or when ...
0
votes
1answer
51 views

Last used index from specific table

how can i take last used index from a specific table and add a +1 to it? database login, table cars I think i need to add it here #### $sql = 'INSERT INTO '.$this->options['db_table'] ...
0
votes
1answer
76 views

Why is this query slow, it seems simple

I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc). In this table are two varchar(100) fields: emailAddress and emailAddress2. ...
0
votes
3answers
144 views

How MongoDB indexes differs from MySQL indexes?

I am a newbie to MongoDB. I know how Indexes works and what it's importance in MySQL. Are MongoDB indexes are the same as MySQL ?
0
votes
2answers
48 views

MySQL Index for searching for NULL values in text fields

I need to do the simple query SELECT id FROM table WHERE blob_field IS NULL. blob_field is a longblob. If I create an index on blob_field with prefix length 1, will this be sufficient to use for ...
0
votes
3answers
40 views

MYSQL Indexes when looking up on alternating sets of columns

I have a table that looks like: CREATE TABLE `connections` ( `connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_id` smallint(5) unsigned DEFAULT NULL, `parent_id` bigint(20) ...