Tagged Questions
1
vote
0answers
34 views
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
3
votes
1answer
56 views
How can I get this query to use it's index?
Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).
As I don't really know what I'm doing I tried adding indexes to all columns in ...
0
votes
1answer
31 views
MySQL - Index on a query with order
I have the following table with >1M rows:
CREATE TABLE `wishlist_place` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`wishlist_id` int(11) DEFAULT NULL,
`place_id` int(11) DEFAULT NULL,
`city_id` ...
1
vote
1answer
44 views
mysql varchar index - will hashing value make it faster?
I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
0
votes
0answers
17 views
MySQL (v5.6.11) InnoDB 5.6.11 restoring indexes during table copy
When i see the profile for my optimize table query here is what i see for the InnoDB storage engine 5.6.11:
+----------------------+------------+
| Status | Duration |
...
0
votes
2answers
33 views
Is there slowdown inserting into an InnoDB table that has no index set?
I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.
Those tables only contain a few thousand rows.
Would it be faster to INSERT data ...
2
votes
1answer
64 views
mysql: need help to optimize my query/table
I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
2
votes
1answer
58 views
In MySQL, does the order of the columns in a WHERE clause affect query performance,why?
I have a query that doesn't use any indexes:
SELECT 32,
guid,
1,
1,
1,
0,
5
FROM test
WHERE level >= 20
AND ( ( fun_GetIndexValue(data, 354) ...
2
votes
1answer
37 views
Do I need to reenter old data after adding an index to a table?
I want to add index to my tables. Some of my tables already have couple thousand rows.
Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
4
votes
3answers
325 views
Should I seperate frequently updated columns
I have a users table that contains users' information and a column named credits that is frequently updated.
On index page I'm showing list of users with basic user information but I don't need ...
2
votes
2answers
56 views
Are there implicit indexes in InnoDB like MyISAM?
If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:
(a)
(a, b)
(a, b, c, id)
Is this valid for ...
4
votes
3answers
120 views
Index before or after bulk load using load infile?
I have a database with over 1B rows and two columns that are indexed (in addition to the PK).
Is it better to have the index pre-defined in the table before the load infile or better to index after ...
0
votes
2answers
62 views
Unique indexed column in where clause and mysql execution after finding first row
Suppose in a table there are 2 columns: login and password
login is indexed as unique index, password not indexed.
query: SELECT * FROM mytable WHERE login = 'Jhon'
query: SELECT * FROM mytable ...
0
votes
1answer
40 views
Index use and column type
I was told today that having a table structure similar to this is bad for performance of index's because of the mismatch of column types
Table 1
id - int (7) PK
Others ....
Table 2
id int (11) ...
0
votes
3answers
17 views
Mysql - How to optimize retrival time in a table
I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table...
mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
...
1
vote
1answer
65 views
I would like to query a range of criteria on multiple columns in MySQL
I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :
We have 3 columns as ...
0
votes
1answer
94 views
Why do MySQL MyISAM table indexes go out of date?
I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes.
Any idea, why this happens?
3
votes
1answer
178 views
Possible INDEX on a VARCHAR field in MySql
I am working in a MySql database, with a table like this:
+--------------+
| table_name |
+--------------+
| myField |
+--------------+
...and I need to make a lot of queries like this (with ...
2
votes
1answer
77 views
How to improve INSERT speed in large table that has unique constraint
I have a simple MyISAM table:
explain entities;
+------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra ...
0
votes
1answer
23 views
Which text-index I should create for xooops engine to achieve better search results?
In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following :
SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, ...
2
votes
0answers
29 views
How can one improve the performance of a query that selects on a low cardinality column in MySQL?
I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
0
votes
1answer
68 views
Mysql. Block/disconnect slow queries
I am experiencing issue.
Someone 'attacked' my server: simply by searching the same phrase with multiple requests.
As it is text search request and database indices are not used, the engine searches ...
1
vote
1answer
94 views
Query is not using indexes on third table in left join
My query is not using indexes on third table(pci promotion_coupon_images) .
i was tried with using index and force index in query but there is no change in result.
any body please suggest me how ...
1
vote
1answer
65 views
index doesn't work properly
I have this query
SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count
FROM single
GROUP BY user_id, stage
table engine is innodb, plus I haven't use any indexes except one key ...
2
votes
1answer
194 views
Unique insert performance: Primary Key vs Unique Index
I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records.
What's the fastest way to insert into the table, by keeping the domain unique ...
1
vote
1answer
34 views
Column order for better index
let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, ...
1
vote
1answer
97 views
MySQL Index Fragmentation
Is there any way to find out the fragmentation of indexes in MySQL ?
If such an output can be obtained, it would be helpful to identify which all indexes need to be rebuilt.
I'm using MySQL 5.1 on ...
1
vote
1answer
37 views
MySql include partition key in index?
Please look at the following Mysql table: http://pastebin.com/b0NDSbdz
I've partitioned the table by sent_at and I'm making sure that most of the queries pass sent_at. do I need to include sent_at in ...
1
vote
1answer
59 views
What are the performance implications of creating an index in MySQL?
The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against ...
3
votes
1answer
142 views
Query optimization with extreme statistic
I have a table:
mysql> describe table_1;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default ...
