Federated Tables Slow? (like 4.5 Hrs For A Query)

Enabling Federated Tables.
I am using mysql 5.0.27-community-nt on windows xp. Federated engine is inactive. How can I activate the federated engine?

View Replies !

---

Federated
I have no idea about creating federation in MySQL, I am a student and I need to create a federated database using 2 machines. Obviously one remote database and one with the federated database on it.

Unfortunately I have been trying to read and understand how to do this. But due to the lack of knowledge and understanding I have with MySQL I am finding it very difficult.

View Replies !

---

Federated Vs Replication
for one of the applications I'm working on, we're splitting users' tables over multiple independent clusters (each user has an own set of tables), but still need a way to keep overall statistics of all users in a single place.

I can think of three options so far:
1) Replicate the stats table (multi-master replication should work, as table doesn't have an auto_increment field) and update stats using triggers;
2) On all secondary clusters, use federated stats table pointing to the appropriate table on the main cluster and update stats using triggers;
3) Modify the application to execute a query, which updates stats on main cluster, every time a row is inserted into/deleted from user's table;

View Replies !

---

Slow Joining Of Two Tables
I am having trouble combing data from two tables. The tables have exactly the same layout, but have different :

mysql> describe MONITORINGUNIT1_DATA;
+-------------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| dt | datetime | | | 0000-00-00 00:00:00 | |
| wg | float(20,3) | YES | | NULL | |
| dflag_wg | tinyint(4) | YES | | NULL | |
+-------------+-------------+------+-----+---------------------+-------+

mysql> select count(dt) from MONITORINGUNIT1_DATA;
+-----------+
| count(dt) |
+-----------+
| 24144 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(dt) from MONITORINGUNIT2_DATA;
+-----------+
| count(dt) |
+-----------+
| 1464 |
+-----------+
1 row in set (0.00 sec)

Very briefly, [dt] contains an hourly date/time stamp representing when the
reading [wg] was taken. [dflag_wg] contains a integer that describes the
data (over threshold, under threshold, etc). The DB is populated
automatically by a Python script that executes once per hour.

If I want to get the overlapping data (with the same date/time stamp) I use
this query: Code:

View Replies !

---

Slow Insert To Tables
I am inserting 70,000 Records to one mysql table, this table is a simple table with no indexes or primary keys (it consists of 3 columns), add those records takes me like 35 mins on a P4 Machine with 2.5GB of RAM. It is very slow comparing to Access or SQL Server.

View Replies !

---

Does Too Many Tables Slow Down Websites
Just wondering if having too many tables in one database slows down a website and would it be better to have seperate databases rather than one big one?

View Replies !

---

Federated Stored Engine
My question, can i enable federated support without re-installing mysql server?
if not, then how can i do it on windows and linux? Please explain briefly .

View Replies !

---

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

View Replies !

---

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

View Replies !

---

Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?

View Replies !

---

Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....

View Replies !

---

Slow Query
i have this query on a website/webapp that has expanded beyond all expectation. It now takes nearly 30secs to return results from the database

SELECT cl_t.Client_ID, Buyer_1_Title, Buyer_1_Prename,
Buyer_1_Surname, Tel_No, Mob_No, Buyer_2_Title,
Buyer_2_Prename, Buyer_2_Surname, Email_Add,
Price_Max, MAX(activity_t.Date) AS lastcomm
FROM cl_t
INNER JOIN cl_want_t
ON cl_t.Client_ID = cl_want_t.Client_ID
AND Agency_Code ='$agencyloggedincode'
AND Deleted = 'N'
LEFT JOIN activity_t
ON Buy_Sell = 'B'
AND Ref_No = cl_t.Client_ID
WHERE cl_t.Sales_Agent_ID = $agentid
GROUP BY cl_t.Client_ID
ORDER BY $order
The problem is the call to MAX(activity_t.Date) AS lastcomm
activity_t holds all known contact with all known clients and as such is a very large table, the call to search through all of these records and return only the date of the last entry for this client is taking the time. If I remove this from the query I get results in 3 seconds.
I have indexing on activity_t.Date & activity_t.Ref_No
Question, is there a way of doing this quicker within the table I already have, or should I create another table that just holds the last update date for each client, and get the date from this much smaller table.


View Replies !

---

Why Is This Query Too Slow?
I find this query to be exceptionally slow(around 2.5 seconds), could some tell me why this is so?

MySQL
SELECT st.profile_views,count( DISTINCT p.ID ) news_submitted, count( DISTINCT pv.ID ) news_voted, count( DISTINCT pcom.ID ) news_commented, u.joined, u.weight FROM users u LEFT JOIN posts p ON p.submitted_user_id = u.user_id LEFT JOIN post_votes pv ON pv.user_id = u.user_id LEFT JOIN post_comments pcom ON pcom.user_id = u.user_id LEFT JOIN stats st ON st.user_id=u.user_id WHERE u.user_id='john' GROUP BY u.user_id
I traced the cause to this line
count( DISTINCT p.ID ) news_submitted (from LEFT JOIN posts p ON p.submitted_user_id=u.user_id)
But when i execute something like this

MySQL
SELECT count( DISTINCT p.ID ) news_submitted FROM posts WHERE submitted_user_id='john'
it is quite fast (around 0.03 seconds)
So why does it slow down when i'm joining the above query with 3 other tables ?
Should i use INTEGER for user_id instead of string like 'john'?

View Replies !

---

Slow Query->Efficient Query
I have a query that is running really slow !!!!
I have joined on Key fields and indexed the tables fully but it is still solw.
--------------------------------------------

select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual
from details d
join poten a
on a.id = d.id
left join recieved p
on d.id = p.id
left join users u
on a.signed = u.userid
where d.paid > '01-Dec-2005'
and d.authorrceived is not null
and d.authorrefused is null
and ((d.payment starting 'E' or
d.payment starting 'e') or
(d.payment starting 'Q' or
d.payment starting 'q' and
p.target = '500'))
order by d.paid, a.signed, d.id

View Replies !

---

Slow Query Log Not Staying On
I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt
command line option to enable the slow query log, started MySQL, and the
option showed up as turned on. Then later I restarted the server, and the
slow query log option went back to being turned off. Is this a Windows
problem in not remembering the service parameter? Has anyone else seen
this?

View Replies !

---

View Slow Query Log
I searched here, google and MySQL docs but did not find an answer.
I'm using MySQL-Front from a Windows platform to administer a remote database. The remote server doesn't have phpMyAdmin or anything like that.
MySQL-Front reports 133 Slow Queries and an average of 15 queries per second. But I dont' know how get more information than that.

View Replies !

---

Slow Query Log Files
I have edited my.ini file to create a file called slow queries. My problem is that when the server starts up i do not get a full list of all slow queries. Is there anything else i need to change? How else can i come about in getting all queries that took a long time to execute? im using mySQL 5.0.9.

View Replies !

---

Query Execution Too Slow!
I have migrated from MySQL 4.1.22 to MySQL 5.0.45. Many of the queries that were working on my old server is working too slow in my new server(MySQL version 5.0.45).

I am not aware why is this happening. Actually there is no error message or query failing. The query is executing, but the time taking to execute the query is too long. This problem is not occurring for all queirs, but only for some complex queires, that is queries with Left Join or Right Join, Group By etc.

I have also checked the MySQL details through SSH(that is by mysql> SHOW VARIABLES;)

Now one thing I found is there is lot of difference in key_buffer_size, table_cache, join_buffer_size, read_buffer_size, read_rnd_buffer_size , table_cache etc between my new server MySQL and old server MySQL.

Will this be the reason for query execution taking too much time.

Can any one guide me why this is happening. The striking thing is that these quires was working very much fine and faster in my old server.

View Replies !

---

Recycling The Slow-query.log
Am using v5.0.22. log-slow-queries=/var/lib/mysql/log/slow-query.log, but this has grown to over 4GB. Can this slow-query.log file by recycled without starting the server? Meaning can I do the following: mv slow-query.log slow-query.OLD.log, followed by a touch slow-query.log WHILE the server is running?

View Replies !

---

Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows.
My table is getting kinda slow here is the query i am using
UPDATE nametable SET
sectionname = replace(sectionname,'".$oldsec."','".$sec."'),
categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ'
where sectionname='".$oldsec."'
and categoryname='".$oldcat."' ;

I am wondering if the same thing is possible with an insert... on duplicate key statement?

I cant seem to get the insert statement to work, but not even sure whether it is appropriate.

With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)

View Replies !

---

Slow Query Execution
Right now I am fazing one issue which is getting hell out of me. I am explaining the issues step by step.

1.I have one site running from last 3 years with large database and there is one main table which has maximum load.

2.Now, I have redesigned the site with lots of changes within this table too.

3.The problem is that the server is same, queries are same but output in the new database is taking 50 times more execution time.

4.I also just copy paste the table, and fire the same query... Strange even that is not working...

Is it something where newly added table or database is having issues in the server?

I have tried all, compared the structures of the data and table but still the query result is too slow on live testing sever. While same query in main site of same server is running perfectly.

View Replies !

---

Slow Query Issue
I'm getting issues with slow queries being generated by a table in my database for my app, this query is consistently generating an entry in the slow query log:

SQL Code:
# Query_time: 2  Lock_time: 0  Rows_sent: 27  Rows_examined: 958SELECT `p_id` FROM `products` LEFT JOIN `products_images` ON (`p_id` = `pi_pid`) WHERE `pi_image_mime` IS NULL AND `p_archived` = '0' GROUP BY `p_id`;

an explain query generates this:

1 SIMPLE ls_products ref p_archived p_archived 1 const 866 Using where; Using mysqlorary; Using filesort
1 SIMPLE ls_products_images ref pi_pid pi_pid 4 shop.ls_products.p_id 12 Using where; Not exists

and a desc generates this:

SQL Code:
pi_id   int(11)      NO      PRI     NULL   AUTO_INCREMENT        pi_pid     int(11)     NO  MUL  0             pi_label   varchar(255)  NO                      pi_primary_image   tinyint(1)    NO         0              pi_sort_order  int(11)  NO         0            pi_micro_blob   mediumblob    NO                        pi_thumb_blob  mediumblob   NO                     pi_large_blob  mediumblob   NO                     pi_zoom_blob   mediumblob    NO                        pi_image_mime  varchar(25)  NO                      pi_date_added  datetime     NO       0000-00-00 00:00:00             pi_filename    varchar(50)    NO                        pi_image_label     varchar(255)    NO

View Replies !

---

How To Enable Federated Storage Engine After Install Mysql
i download the mysql version 5.0 last week and select complete one to install mysql.... after that i need to use the federated storage Enegine but the query browser said mysql federated feature is disable.

View Replies !

---

Starting MySQL Server With Federated Engine Support
I have MySQL service installed on my machine. I want to start the MySQL server with FEDERATED engine support. According to MySQL Federated Engine, I should start the server with --federated option. Where do I need to change this?

I tried adding federated to the my.cnf file. Then Federated engine started working and InnoDB stopped working.

After adding this line, I executed
MySQL Code:
SHOW ENGINES;
There, I couldn't find InnoDB in list.

View Replies !

---

Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out.
The query I'm running is too slow...can anyone tell me what I can do to
speed it up..if I can at all? I was wondering if because ZipListMatrix has
3.3 million rows that 8 seconds is all the faster it's going to be. Any
help is greatly appreciated! I have already "optimized" the tables.

View Replies !

---

Server Slow When Exec Following Query
i try to tweak mysql server but the process still very slow to exec o following query

exec time 2min for 11k row in two table

update uploaded,uploaded_job set uploaded.export = 'yes' WHERE uploaded.work_order =uploaded_job.work_order and uploaded_job.claim_status IS NULL

i had try indexing and optimise my database result still the same.

View Replies !

---

Slow Query Wile Using Indexes
i am having a problem with a aparently normal query, i am using indexes and it is still very slow, as the table grows biger and biger i am afraid the system cant hadle it...

Here is the table data: ....

View Replies !

---

Slow Inefficent Query (FIXED)
I have this query which pulls members from a table of 1200 members details, filtering out those without and email address and those that don't want Newsletters, and also any whose email address exists in a second table (emails that have hard bounced previously)

My first attempt took an age and returned about 10,000 members, clearly loads of repeated rows

My 'final' attempt with a DISTINCT shoved in works perfectly but is really slow, PHPAdmin says it takes 1.1s but on the website the data doesn't appear for over 30s, all the other sundry queries on the same table work nice and quick on the same website using the same php to generate the table of results

The googling I've done suggests (to me) that there's nothing wrong with the query but I suspect that there's far too much checking of fields going on from the pre DISTINCT query's results ....

View Replies !

---

Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
I installed mysql 5.0 and need to set up slow-query-log and other logging options.

Here is what I did. But I dont see it working yet.

1. cd /var/db/mysql
2. chown mysql slowquery.log
3. touch /usr/local/etc/my.cnf
4. chown mysql /usr/local/etc/my.cnf

vi my.cnf

[mysqld_safe]
-u mysql
--log-slow-queries=/var/db/mysql/slowquery.log

so now when I type:

mysqladmin shutdown
and than

mysqld_safe &
my sql restarts but the log files are not being used.
also - how do I know if my my.cnf is being used at all?

View Replies !

---

Turning On Slow Query Logging?
Background: I paid a young admin set me up on a database server. He installed the basic I needed for the server...at my request...No Cpanel...mysql and apache and some tight security w/o even a domain name to SSH into. Unfortunately, he's a busy kid, and teens sometimes don't realize that people depend on them...and well, I can't really seem to get him to do much so I gave up and figured it's a good way to force me to learn all this myself...
Well anyway, now I want to turn on Slow Query logging. But before I do that, I need to know how MySQL is running. Is SQLogging turned on already? Where is it logging to? So first thing I want to look up is, when the server is rebooted, what's the command to restart mysql? No clue. How do I change the setting? And of course, the server is production, so when I make the change, it needs to be quick, it needs to be smooth, and I need to be able to roll back to the previous config if necessary.
I'm running Redhat Enterprise.

View Replies !

---

Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default Ɔ',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:



CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:


SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:



SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:



mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

View Replies !

---

Help Rewriting A Slow Phpbb Query
I have a "glance" or "Recent Topics" list on my forums that have become fairly complex. I modified an already feature rich glance mod to allow users to select individual forums to exclude from showing topics in the list. As well when users are members of certain forum groups, they see topics from the group forum in the list, and they are highlighted a different colour.

The main SQL query to create the list often is showing up in the MySQL Slow_query log and I'm pretty sure is the main cause for the page loading slow.

I am no mysql Guru, so I thought I would seek the advice of some to improve or totaly rewrite this slow query.

PHP

$sql = "SELECT     
    f.forum_id, f.forum_name, t.topic_title, t.topic_id, t.topic_last_post_id, t.topic_poster, t.topic_views, t.topic_replies, t.topic_type,
    p2.post_time, p2.poster_id,
    u.username as last_username,
    u2.username as author_username
FROM "
    . FORUMS_TABLE . " f, "
    . POSTS_TABLE . " p, "
    . TOPICS_TABLE . " t, "
    . POSTS_TABLE . " p2, "
    . USERS_TABLE . " u, "
    . USERS_TABLE . " u2                
WHERE
    f.forum_id NOT IN (" . $forumsignore . $glance_recent_ignore . ")
    AND t.forum_id = f.forum_id
    AND p.post_id = t.topic_first_post_id
    AND p2.post_id = t.topic_last_post_id
    AND t.topic_moved_id = 0
    AND p2.poster_id = u.user_id
    AND t.topic_poster = u2.user_id
ORDER BY t.topic_last_post_id DESC";
$sql .= ($glance_recent_offset) ? " LIMIT " . $glance_recent_offset . ", " . $glance_num_recent : " LIMIT " . $glance_num_recent;


The "NOT IN" list varies per user, but here is an example:
NOT IN (77,75,76,25,26,37,63,64,66,67,67,1,25,26,37,70,28,75,76,78)

View Replies !

---

Can't Turn On Slow Query Logging
long_query_time = 1
log-slow-queries = /var/lib/mysql/slow_queries.log
Is the above syntax not correct for enabling slow query logging? All examples I've seen have the dashes in the second variable and underscores in the first.

When I restart MySQL with those lines in my.cnf, it fails to start, but writes nothing to its error log.
/var/lib/mysql/slow_queries.log exists, is owned by mysql, and has read/write permission.

View Replies !

---

815,000+ Records, DB Table Query Too Slow
I have a database table with about 815,000 records in it, each record containing the source of individual webpages (with all the tags and multiple spacing stripped out leaving just the words). At first my match boolean queries were fine but once I got to around 100,000+ records it became too slow to use for a web-based search tool. I was able to separate the data into 10 tables and then use AJAX to run the match query on all 10 tables at the same time and that has increased performance.

My question is, is it possible for me to increase the performance for the 1 table some how instead of resorting to separating the data into multiple tables? Is MySQL the right DB for this kind of setup, would MSSQL or PostgreSQL perform better for larger sets of data? I'm not sure how MySQL 5 clustering works,

View Replies !

---

Slow Performance :: Navicat To Import Tables From An ODBC Connection
have been using Navicat to import tables from an ODBC connection I have. The import goes extremely fast but once the data is locally stored and I try to manipulate it by joining tables, the program goes slower and sometimes even crashes it.

If the same data is however imported into a table in MS Access, and a query is created it runs fine. I would like to know if there is a way to improve the performance of my MySQL database as the reason I was using it is because of its scalability. I was wondering if there was something I was doing wrong or could do better.

The Server the data is downloaded to right now is has the following specs:

Windows XP Pro
Intel Core 2 CPU 2.13 Ghz
2 GB RAM

View Replies !

---

Do Parallel Inserts And Selects Slow Down A Query?
I've been facing problems with a query which I think is relatively well optimized but has been performing really slow as of late as the size of the table has increased to more than 1GB. The MyISAM table is updated every few seconds, only a few rows, and a different set of rows are selected every few seconds.Wwould that cause a slowdown and if so, what is the solution?

View Replies !

---

Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do:

There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns.

The most common query is SELECT data FROM table WHERE n1 = ?

The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column.

Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset:

SELECT COUNT(1) AS total FROM table WHERE n1 = 3;
index = random number from 1 to total
SELECT data FROM table WHERE n1 = 3 LIMIT index,1;

How can I speed this up? I need it to take less than half a second if possible. Thank you.

View Replies !

---

Speed Up Query, Order By Column Is Too Slow!
I have a query that takes ages:

SELECT tbl1.a, tbl2.b
MATCH (tbl1.a)
AGAINST (
'someValue'IN BOOLEAN MODE
) AS score
FROM `a` , `b`
WHERE tbl2.b = 'someRestriction'
ORDER BY score DESC, b.tbl1 DESC LIMIT 20

but because there are thousands of rows it takes ages to do both of the orders, however just : ORDER BY score LIMIT 20 -- is really quick
and : ORDER BY b.tbl1 LIMIT 20 -- is really slow

is it possible rearrange a constantly updated database so that its naturally ordered by b.tbl1 (so there will be no need to do the ORDER BY b.tbl1 query each time), or are there any other methods to speed this up (putting it all in one table instead of 2, is that significant?)

View Replies !

---

Slow Query Execution With Strange Issue
Right now I am fazing one issue which is getting hell out of me. I am explaining the issues step by step.

1.I have one site running from last 3 years with large database and there is one main table which has maximum load.

2.Now, I have redesigned the site with lots of changes within this table too.

3.the problem is that the server is same, queries are same but output in the new database is taking 50 times more time.

I have tried all, compared the structures of the data and table but still the query result is too slow on live testing sever. While same query in main site of same server is running perfectly.

Have any one fazed such and issue? Please help me out of the situation as site is ready but I m not able to put new version live.

View Replies !

---

Slow SELECT Query INNODB Table
I have a couple hundred connections doing "SELECT [Char36 Field], [LongLong Field], [Long Field] FROM [Connection Specific Table] WHERE [NonIndexed VarChar36 Field]=[Value]". Notice that the table the select statement is being called on is unique to each connection. These tables have less than 10000 records, but this statement can sometimes take over an hour to execute.

It is an INNODB table.

View Replies !

---

VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
I have the following MySQL query, but it is VERY VERY slow and seems to be crashing the server. There are 300,000+ records in the 'tracker' table.

SELECT sites.*, SUM(if(tracker.type='view',1,0)) AS numberOfViews, SUM(if(tracker.type='click',1,0)) AS numberOfClicks, SUM(tracker.revenue) AS totalRevenue FROM sites LEFT JOIN tracker ON tracker.site_id = sites.id GROUP BY sites.id ORDER BY sites.domain_name


View Replies !

---

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

View Replies !

---

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

View Replies !

---

Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop.

$sql = "SELECT * FROM mytable order by points desc limit 10";
$rec = mysql_query($sql) or die(mysql_error());
$datas = mysql_fetch_array($rec);

do{
$sq = "Select * from secondtable where linkid = '$datas[id]'";
$rst = mysql_query($sq) or die(mysql_error());
$rows = mysql_fetch_array($rst);
echo "$rows[somefield]";
}while($datas= mysql_fetch_array($rec));
This works perfectly but I want the second query to be out of the loop if there is a way and how.

View Replies !

---

Two Tables In One Query
i'd love if anyone could help me out on the following situation:
I do have a db with two tables for price comparisations - my example:table 'items' contains an entry with the itemno '123' and a basic price of '5.00' plus additional stuff like description etctable 'prices' contains 3 entries for the itemno '123' with the prices '6.00', '10.00' and '20.00'
Now i need to catch several data from the table 'items' and additionally the highest price for the item '123' in one query.
The tricky thing is, that an item possibly could have no entries in the 'prices' table and in that case the kinda default price from the table 'items' should be taken.

View Replies !

---

Two Tables--One Query
I've got two tables: carddb_games and carddb_cards. Their structures are like so:Code:

carddb_games {
game_id tinyint(3)
game_name tinytext
}
carddb_cards {
card_id smallint(5)
game_id tinyint(3)
card_num tinytext
card_name tinytext
card_effects tinytext
card_special tinytext

Is it possible to make a query such that it will go through each game in carddb_games AND return a count of how many cards exist with a matching game_id within the carddb_cards table?
I am not sure as to how to go about it, if such is even possible.

View Replies !

---

Can You Query Against 2 Tables?
In one table I have a list of users and in the other a register to keep track of when they are in. The query I need to build will check that the user belongs to a particular group (in the user table) AND that there is no data against there name for today i.e. date("YY/mm/dd") in the register table....

View Replies !

---

Query From Two Tables As If It Were One
I have two tables with identical columns videos and photos.

I basically need to do this:

SELECT * FROM MERGE(`videos` `photos`) ORDER BY `created` DESC

So they can be displayed in chronological order on a result page.

View Replies !

---

Need To Query Two Tables
I am new to MySQL, but I'm sure the pros here can figure this one out pretty easily.

There are two tables; one that lists all of the clicks from all members and one that lists all of the links.

Basically, when the member logs in I want them to see a list of links that they have not clicked on.

The clicks table has a user_id and link_id column.
The links table has the link_id column and some other info.

View Replies !

---

Using Many Tables In A Query
I'm developing a script, which creates a bunch of tables for each registered member. And I need to sum up some statistical information, like records count, based on tables of each user, say: user1_posts, user2_posts and so on.

How efficient would it be to use a query with its from clause consisting of thousands of tables in this case? Should I look for another way to do this?

View Replies !

---

Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order.

I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So:

SELECT * FROM order_log WHERE product's group_id = 1.

View Replies !

---

FLUSH TABLES Via C Query API
Using FLUSH TABLES via the C query API mysql_query() hangs if the table is
locked already. That is to say, nothing prevents me from running a LOCK
TABLES twice; it won't tell me "it's already locked, don't try to run a
FLUSH".Anyone know how to find out if a table is already locked? And don't say
"call the DBA" :)

View Replies !

---