Tagged Questions
0
votes
2answers
39 views
Is there a good reason to store big text data outside database?
I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb.
This data is only used when loading single record (like article or blog ...
0
votes
1answer
48 views
Large INSERTs performance optimization
I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query).
The queries are send ...
0
votes
0answers
32 views
Are there any disadvantages to partitioning on financial year?
Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year.
The majority of ...
0
votes
0answers
34 views
Optimising table efficiency format integers only [closed]
I currently have a table with 1,136,053 rows and 5 columns in a MySQL table.
They are all integer values and appear to be slowing down as I am increasing the rows.
I have indexed each of the ...
2
votes
2answers
101 views
Why does adding LIMIT to my query make it crawl?
Simple query:
select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by scores.user_id ...
1
vote
1answer
61 views
Query optimization when no row is returned
I am using explain to figure out what is happening in my query which is:
explain select t from c where u1_id = 1 group by t;
I see "Using where" in the Extra column of the resultset. But this only ...
0
votes
0answers
65 views
InnoDB tuning with 1G of ram limit
I am trying to calculate variable moving averages crossover with variable dates.
That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
0
votes
0answers
18 views
Table partionning problem
I need your help for a partionning problem. We are using a "log_event" table which log a lot of events sent by our players (subscription, connection, play, share, etc...). We insert 5,000 row / s in ...
1
vote
1answer
36 views
Will OPTIMIZE TABLE have any impact on my data?
I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.
But before doing so, I would (of course) like to know if ...
2
votes
1answer
41 views
Optimize UNION query in MYSQL
I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, > 35 ...
3
votes
1answer
108 views
Simple MySQL query randomly takes forever
I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will ...
2
votes
1answer
73 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 ...
0
votes
1answer
32 views
Need ideas about OPTIMIZE TABLE
Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of ...
1
vote
0answers
61 views
How can I join one to one to (many to one) without using group_concat
I have a table structure as follows:
--
-- Table structure for table `emails`
--
CREATE TABLE IF NOT EXISTS `emails` (
`ID` int(5) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE ...
0
votes
1answer
47 views
Optimizing ORDER BY for simple MySQL query
I'm trying to optimize this really simple query, and it gives me grief for a day now :(
Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
0
votes
1answer
58 views
Database schema design help needed
I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.
This application ...
0
votes
1answer
42 views
Memcache implementation
I have a Wordpress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get ...
1
vote
1answer
35 views
extra steps after changing storage engine and adding index
Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases.
I have a php/MySQL site uses the MyISAM storage engine. It ...
0
votes
2answers
191 views
MySQL tuning (my.cnf) for very large tables and ad hoc queries [duplicate]
We are an email marketing company and recently switched our setup to MySQL. We need to configure mysql (my.cnf) for extreme performance.
We have tried to configure my.cnf but heavy queries can get ...
1
vote
1answer
36 views
Mysql performance for a nullable unique id column in a large table that references a record in another table
We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the ...
0
votes
1answer
101 views
MySQL subqueries that use range based on values of main queries don't use indices properly
I think I've isolated a problem that has been affecting many of my queries lately.
And would like some help to figure out a solution for this.
Ok so my findings are that a normal query that runs very ...
0
votes
2answers
155 views
optimize big sql query
My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
0
votes
1answer
63 views
Optimization of a select statement
I'm using MySQL and have a table user_data like this:
user_id int(10) unsigned
reg_date int(10) unsigned
carrier char(1)
The reg_data is the unix timestamp of the ...
1
vote
2answers
76 views
Database and query optimizacion
I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
2
votes
1answer
62 views
Slow Queries Not Logging
I am attempting to enable slow query logging on our server in order to identify any queries that could use optimization. Sounds simple enough, however my file is not being written to. I get no errors ...
1
vote
1answer
108 views
Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?
I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the ...
0
votes
3answers
222 views
Mysql optimization help
I've tried optimizing mysql as good as I could, but apparently I'm not to good at it :-)
So I come to you guys for help.
Server specs are:
AMD Opteron CPU 8 cores
16 GB RAM
2x2.000 GB HDD 7.200 RPM ...
2
votes
0answers
138 views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
1
vote
4answers
108 views
Normalization of DB
I have designed a database using MySQL likewise
My question is what else i can do to normalize the DB, for now i am using InnoDB engine for the table to maintain the relation among different tables.
...
2
votes
1answer
225 views
Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields
I have 3 simple tables with polls, users and votes. I'd like to show all polls ordered by clean count of votes (sometimes either poll_id or user_id don't get inserted).
Furthermore I'd like to show ...
3
votes
1answer
149 views
Query optimization with extreme statistic
I have a table:
mysql> describe table_1;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default ...
0
votes
0answers
71 views
What is faster - multiple access of table or using temporary table?
I have created two tables
create table one(ref_no int, name varchar(100), design varchar(100));
create table two(SNo int auto_increment primary key, use_date Date, ref_no int references one(ref_no), ...
0
votes
1answer
750 views
Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?
I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation.
Would it be useful to run -check with this option or does the -optimize already take care of that?
I am only ...
1
vote
1answer
95 views
Proper indexes or anything else to optimize
I need help to optimize the query to avoid using "Using temporary", "Using filesort".
CREATE TABLE `target_bahrain_pepsi` (
`id` int(11) DEFAULT NULL,
`col_value` varchar(50) DEFAULT ...
3
votes
1answer
339 views
Subqueries run very fast individually, but when joined are very slow
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
0
votes
1answer
124 views
Optimising MySQL query with lots of self-joins
I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved.
In some cases it can be said that one entity is a constituent of a ...
3
votes
1answer
308 views
Mysql Server optimization tips needed! (InnoDB)
I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. ...
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
2answers
142 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
1
vote
1answer
100 views
Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?
I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
5
votes
1answer
135 views
Walking a BTREE index as far as possible in MySQL
Suppose one has a column of words on which one builds a BTREE index:
CREATE TABLE myTable (
words VARCHAR(25),
INDEX USING BTREE (words)
);
LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
0
votes
2answers
194 views
Slow query with straight_join for small result
I have a performance-problem with a query in my application and I don’t understand the behavior of mysql.
The query consists of different joins, especially the join to the mentioncache-table. If the ...
4
votes
1answer
227 views
How much does table size affect performance?
Lets say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often.
By not regularly archiving or deleting these ...
2
votes
1answer
627 views
MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?
I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
0
votes
0answers
39 views
mysql select query optimization [duplicate]
Possible Duplicate:
mysql select query optimization
I have two tables, testa and testb.
CREATE TABLE `testa` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
...
0
votes
0answers
112 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
2
votes
2answers
79 views
How to optimize a log process in MySQL?
In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns ...
-3
votes
0answers
31 views
Doubt :: my sql performance tuning [duplicate]
Possible Duplicate:
MySQL performance tuning
I would like to optimize my MySQL DB and I identified some important points as follows:
enabling query cache,
using memory storage (I know ...
1
vote
1answer
140 views
Is a query with UNIX_TIMESTAMP using indexes in mysql?
If I use
.. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW()
in a SELECT, does this query still make use on an index, set on some_timestamp ?
0
votes
1answer
103 views
MySQL query optimization
I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be ...
