Join Too Slow

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 !

---

Slow Join On Otherwise Fast Views?
select * from vw_fielddata

takes 16ms to return just four rows, why does

select
*
from
vw_fielddata firstname
join
vw_fielddata secondname
on
secondname.AccountID = firstname.AccountID

take over an hour? The views themselves contain joins on tables and further views, and although AccountID isn't a primary key from the tables it's gathered from it is a foreign key in them (I assume this makes it indexed?).

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 !

---

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 !

---

How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
I used subquerys because they made more sense to me, until the table got "a lot" of data in it (not really... just 1000 entries) - then all querys including subquerys slowed down to 4-5 secs EACH!! :) This is insanely slow.

What I am doing here below is looking into what messages a user has already read in the subquery, so that none of the ones he already has read will EVER again show up for him.

So, how do I rewrite this subquery:

NOT IN (SELECT reffen FROM $readt WHERE sender = $nr)

From this entire SELECT:

SELECT halfref, brokensms, DATE_FORMAT(arrived, '%y'), DATE_FORMAT(arrived, '%m'), DATE_FORMAT(arrived, '%d'), DATE_FORMAT(arrived, '%H'), DATE_FORMAT(arrived, '%i'), priv FROM $halft WHERE sender = $nr and halfref NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) order by id desc limit 1

Into a faster join of some sort?

I'd aprichiate if you told me what each part of the rewrite actually does, because I been reading about joins for a day now and still don't get them at all!

View Replies !

---

Outter Join, Inner Join, Left Join, Right Join
I don't see the point of making so many kinds of join, that drives me confusing reading the mysql document.

View Replies !

---

Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results:

QUERY 1:
SELECT COUNT( o.orderID )
FROM order o
WHERE DATE( o.orderDATE ) = ��-01-04'
AND o.orderSTATUS = 300

yields 161

QUERY 2:
SELECT COUNT( o.orderID )
FROM order o
LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID
WHERE DATE( o.orderDATE ) = ��-01-04'
AND o.orderSTATUS = 300

yields 175

QUERY 3:
SELECT COUNT( o.orderID )
FROM order o, credit_card cc
WHERE o.orderID = cc.orderID
AND DATE( o.orderDATE ) = ��-01-04'
AND o.orderSTATUS = 300

yields 157


View Replies !

---

LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want.

The tables are:
table services
service_id
service_name

table services_provided
service_id
service_date (date field)
cust_id
service_quantity

I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time

The query:

SELECT service_date, service_name, service_quantity
FROM services
LEFT JOIN services_provided ON services_provided.service_id = services.service_id
WHERE cust_id = $cust_id
AND MONTH(service_date) = 10
AND YEAR(service_date) = 2007
GROUP BY service_id
ORDER BY service_id
(Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.)

I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table.

But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results.

Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.

View Replies !

---

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

View Replies !

---

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that does not accept an ON condition, so I have shifted the clauses to the WHERE and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this approach it makes the code clearer regarding intent. Code:

View Replies !

---

Cross Join + Full Outer Join ?
I desesperately need to cross join 2 rows in a table, like this table :

table values :
env type value
---------------------
env1 a 1
env1 b 2
env2 b 3
env2 c 4
env3 c 5

-> to get another table crossed by env and type like this :

table results :
a b c
----------------------
env1 1 2 null
env2 null 3 4
env3 null null 5

i tried requests using cross join and full outer join but no way, FULL OUTER JOIN is unknow by mysql, indeed i doubt this is the good solution...

View Replies !

---

Slow
What generally would be the reason why all my db driven sites are running slowly or even hanging. I am on braodband speed but just changed hosts.

View Replies !

---

Update Too Slow
I need to update 25 * 5000 records, if I do one at the time it takes too
long time, do any one have a good proposal ?

View Replies !

---

Very Slow Select
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong?

I'm also including the dump of the table definitions. This is a cd cataloging database.

Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:

View Replies !

---

MySQLdb Slow
I have a Python program that parses a file and inserts records into a
database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my
parser runs *really* slow. Writing out to CSV files is fine, but when I
try to insert the same records in a MySQL5 database, it slows to a
crawl. Using MySQL 4.1 seems fine. The data seems to be inserted
correctly, it's just really slow.

View Replies !

---

Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB.

Using the mysql administrator gui to create a backup, everything goes
fine, and restores quickly.

Using the command line:

mysqldump %dbname% --single-transaction > %dbname%.sql

creates a file about 15% smaller than the gui produces, and is
EXTREMELY slow to restore. I have tried adding locks, skip opt,
everything. What does the gui use for a command to create this dump?

View Replies !

---

HEAP So Slow
I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns...

ID | Title

title is indexed. When i run a query like this
------------------
SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds...
------------------
Even a single like statement just on `index` (heap) takes 3-6 seconds.

Here's the table stats...
----------------------------
Data 397,442 KB
Index 24,639 KB
Total 422,081 KB
----------------------------
Why??

View Replies !

---

Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.

View Replies !

---

Slow MySQL
mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that.

MySQL said: Documentation
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

View Replies !

---

Slow Db Access
I have worked with a few mysql dbs on different servers but i have recently been asked to work with one on nicnames. It seems horribly slow. Working in phpMyAdmin (which i had to install myself) it takes ages when i want to do anything. View the table structure, view the data..etc. Any way i can test the speed so that i can compare it against another server i work with and proove there is a speed issue and take it to nicnames cus it is crazy and is going to affect the speed of the website!!

View Replies !

---

Slow Insertions
I have a MyISAM table with fixed width rows (ie no vars). I also have keys disabled. I am running a huge data load process. When it first started it was inserting about 38 records a second. Now, after about 200,000 records, it speed is about 22 recoerds per second. Whats going on here? and how can I improve it?

I am loading about 15 million records, so the difference between 22 per sec and 38 per sec is SIGNIFICANT (ie 2 days).

View Replies !

---

Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table.

Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.

View Replies !

---

Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form.
My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot?
Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?

View Replies !

---

Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished)

select date from temps where date in (select distinct date from observations where camera like "a")

The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?

View Replies !

---

Slow Connection
I build an application and installed it on many machines. In every machine except two, the program works without problems. On this two, the connection with database is too slow.

I saw the opened doors with 'netstat' and the computer opens about 5 or 6 ports (to the port 3306 of the mysql server) before sucessfuly connect with MySql Database and execute the sql. I don't know what could be happening. I realy need to fix this because the progrm is too slow with this error. Could anyone know what could be happening??

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 Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor)

This is the query :
SELECT NPL, PP FROM P_A INNER JOIN ACTIONS
ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in
(SELECT NA FROM P_A WHERE NP ='ABC')

P_A has 5 columns and 12000 rows
Actions has 5 columns and 770 rows

Any suggestions ?

View Replies !

---

Slow Request
I want to run the following request:

mysql> select count(*) from fingerprints where fingerprint in (select fingerprint from fingerprints where id_file=3263);

where fingerprints is the following table: ....

View Replies !

---

Why Does MySQL So Slow
I just changed to use MySQL few days ago but it was a bad idea. My server now is running very slowly with the database. I'm using Perl5 and DBD::Mysql in my script. The system is Linux9, Apache2.

I looked at these mysql pid and saw a lot of activities (about 400) while there are more 100 users online at this moment and lots of running under a the same pid number.

View Replies !

---

Slow Max() Selection
I have a large table with some fields. Two of them are:

year char(1) && year's rightmost character
OrderNo integer

Both are keys (BTREE).

I need to select the last order in the year.

select max(OrderNo) from ORDENES where year='8'

While the year is growing the selection speed id decrement (12 seg).

View Replies !

---

Slow Connect
Does anybody know why it sometimes takes more than 10 seconds to connect to a database and sometimes it just takes half a millisecond?

View Replies !

---

Slow Connections
I am using MyODBC-3.51.11-2-win on Win 2003 OS. I am not able to see all of the connections in the list under the System DSN tab. The connections that show allow the ASP pages to run at an expected rate.

However, the ones not showing in the list are running extremely slow. If I attpemt to recreate the connection I am told that the connection already exists and asks if I want to replace the existing connection. Wheter I click yes or no the connections do not show and the pages run slowly. How do I get them to show or resolve the issue. The ASP code is the exact same SQL statements and connection strings as the in previous applications.

View Replies !

---

MySQL Slow
I had downloaded a few years back mySQL v3.51 installed but never used it. Now I wanted to convert some B-TREE databases to mySQL and did some testing via ODBC to insert 70,000 records: My results:

MS ACCESS: ~60,000 msecs
MYSQL v3.51 ~18,000 msecs

Impressed with the speed, I went ahead and got the latest MySQL v5.1. Uninstalled the older version, I had nothing there to preserve, so I did a simple new install with MySQL v5.1. I noticed the size of the files and BINEXE increasted by 1,000,000%. Ok, Bulky. Not a problem.

I reran the same ODBC test, and now I got:

MYSQL v5.1: ~450,000 msecs or 7.5 freaking MINUTES!

What the hell happen? Nothing was done. I'm knew to MYSQL. I just installed it with all the defaults. I did choose "developer's machine" for the "optimizer wizard"

I can't redistribute MYSQL v3.51 and force it down people's throats! I have to use what they are using already, if already installed. Not even my current system takes 1 minute to add 70,000 records. Why 7.5 minutes? All it is simple inserts/free statements.

View Replies !

---

What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?

View Replies !

---

MySQL Slow Log
I have the long query time set to 15 yet MySQL is still showing results with a query time of 0 in the slow query log.

It says enter time in "seconds" in the MySQL Administrator but did it mean in milliseconds??

View Replies !

---

Slow Update
The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records.
UPDATE items SET item_views = item_views + 1 WHERE id = 5897;
is there any way I could speed this up? Some setting I could change to make MySQL faster for this?
The field "id" is the primary table key.

View Replies !

---

Slow Authentication
MySQL V 5.0.18 on SUSE 10.1

I'm not a complete *nix noob, but I sure as hell ain't a *nix or MySQL pro.

This is a new installation. Everything screaming fast. Unless it deals w/authentication.

Try to get in w/SQLyog from W2K locally ... intitial connection takes ~20 seconds. Then everything screaming fast.

Web Server (W03) attempts to connect via MyODBC ... same result ... initial connection takes ~20 seconds. Subsequent queries screaming fast.

VNC into the box at any time ... everything fast. (would seem to eliminate network/connection issues)

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 !

---

How To Log-slow-queries
I'm running MySQL 5.0.

Now I would like to log-slow-queries. With MySQL 4 I used

mysqld --log-slow-queries --log-long-format start

but now I get following note:

mysqld: Too many arguments (first extra is 'start').
Use --help to get a list of available options

It looks like starting and stopping now only works with

etc/init.d/mysql start

View Replies !

---

Service Pack 4 Slow Down
I'm running Windows 2000 Server and MySQL 4.1 and all has been great
until yesterday when I installed Windows Service Pack 4. Now when I
query a database it displays very slowly. Before the service pack it
was almost instant.

Everything works properly, it's just very slow.
Anyone heard of anything like this? If I use the MySQL command line and
query the database it's very fast. The slow querys are from php pages.

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 !

---

MySQL Slow In Windows NT4
I am running the Apache web server, MySQL v4, and PHP on an NT4 server.
Apache runs great, but the auction software I am using (Web2035 Auction
software written in PHP) is very, very slow.

Sometimes it takes 20-30 seconds to bring up an auction page from the items
table which has less than 200 records in it.

Can anyone can give me some pointers on where to start looking? (I don't
know if the bottleneck is with MySQL or PHP or what I might need to look at
to enhance the performance of either package.)

View Replies !

---

Slow Database Retrieval
iwhat would be the cause of our problem? i don't thnk it has someting
to do with the way i run my queries from my applications since i do
not encounter such problems on our old machine..Also i tried to
execute queries from my sqlyog :
ex:
select * from db_town
result: 8902 rows() in 2687 ms
where on the old server it should take only about 421 ms

select * from diagnostics_detail
result: 42499 rows() in 27609 ms
where on the old server it should take only about 4375
ms.

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 !

---

Slow When Using Desc Clause
I'm scrolling by Order_# where I find and display on screen the < (lesser
then) value of Order_#. Here's the syntax:

"Select * From Orarchd Where `Order_#` < " & lngOrderNumber & " Order By
`Order_#` Desc".... Order_# is the primary index.

lngOrderNumber is the current value of Order_#. Here's the problem ...it
takes forever to find the next record (at least 10 seconds)

View Replies !

---

Extremely Slow INSERTs
Im inserting about 4,500 records into a table. There are only three fields each containing text.

Im using PHP to insert these records, but it's REALLY slow (can take like 5 minutes to insert line by line. I also tried generating a SQL file and inserting like that, but it still takes a couple of minutes.Is there anythign I should be checking in the mySQL config or something to make this faster??

View Replies !

---

Connect To A Database Is Slow
I can connect to the mysql server really fast. But when I select a database, the server will wait for about 10 seconds before connecting obviously, imagine that kind of wait on a website. loads of users will click off.

View Replies !

---