Tagged Questions
0
votes
1answer
15 views
MYSQL - Using simple inventory number convention as Primary Key
I have an inventory with three numbered groups like so:
Group 1: 0-999
Group 2: e0-e999
Group 3: v0-v999
Can I simply make a one-column table to store this information and make that column the ...
0
votes
1answer
65 views
Am I wrong in table design or wrong in selected index when made the table?
I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
1
vote
0answers
59 views
Can't get rid of filesort on joined query sorting on second table
For some reason, this query:
EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;
says it ...
0
votes
1answer
55 views
Two identical MySQL tables, one not using indexes
I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data.
Schema of one table
CREATE TABLE `measuresHistory` (
...
1
vote
1answer
88 views
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 ...
1
vote
1answer
26 views
Is it better to add an index and search via the index or add a unique constraint and let an insert fail?
I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job:
mysql> DESCRIBE job_views;
...
1
vote
1answer
47 views
MySQL: logging queries which would execute without using indexes
I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, ...
3
votes
1answer
124 views
What are the disadvantages if indexes are added on all columns?
I know, that it is not a good design to set too many indexes, but just for understanding the theory:
What exactly are the disadvantages if you add an index on all columns in a table in MySQL?
Are ...
2
votes
0answers
34 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 ...
3
votes
1answer
56 views
What is a suitable index for this query?
Below query is taking long time - around 540 seconds.
The table did not have any indexes. I created indexes on columns in the WHERE clause and other fields but the query is not using these indexes.
...
2
votes
1answer
247 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
38 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
63 views
Performance impact of including result column in indexed columns
I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of:
texelid ...
1
vote
2answers
107 views
How to optimize this query?
Query:
Select *
from `t_event`
where `create_user_id`=7
and (`event_create_date`)=('2012-12-18 00:00:00')
and `event_type_cd`=11
and `domain_id` =602
and `job_id` =1
limit 1
Table structure:
...
2
votes
2answers
92 views
Optimizing index creation
One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns.
An example case would be:
table1
...
1
vote
1answer
138 views
Simplify and optimize a complex query
Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take ...
1
vote
2answers
362 views
Best way to organize compound indexes on partitioned table (date by range)?
I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...
1
vote
1answer
99 views
What would be a optimal index for this query in mysql
I'm trying to optimize this query.
SELECT * FROM sales_reps
WHERE account_id = 1
AND (state = 'enabled')
AND (clients >= 5 OR revenue >= 10000)
ORDER BY name, platform, client_group, ...
5
votes
2answers
535 views
How can I improve the speed of a query on a 20 million+ row table?
I have a query that is used for getting internet traffic statistics of certain IP addresses.
There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
2
votes
1answer
95 views
Are two indexes needed?
Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.
Which indexes should I use? Should I have two ...
3
votes
2answers
91 views
MySQL using a index referencing a different column
I have a large table which uses a versioning system in the table. When a record is updated a new record is inserted with the old data and the original record is updated ( so to maintain the same ...
3
votes
2answers
248 views
Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?
In this query:
select count(*) from largetable;
a secondary index is chosen:
mysql> explain select count(*) from largetable;
...
4
votes
3answers
142 views
Most efficient (or meaningful) way to index grid-based data
I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
