Query With 3.3million Rows Is Slow?

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)

---

Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'.
About half the rows are 'yes' and half are 'no'.
Table is indexed on 'stamped'.
We just need the count on 'yes'.

SELECT count(*) from T where stamped='yes'

or <>'no' or >'n'... is very slow.

Is there a better approach / query to get a fast result?

---

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.

---

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'?

---

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?

---

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 ....

---

Slow 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

---

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=&#391;'
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)

---

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.

---

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 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.

---

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.

---

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.

---

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)

---

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 &#390;',
`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,

---

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?

---

Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team.

The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem.

The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit.

Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?

---

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 ....

---

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

---

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.

---

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?

---

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

---

Several Rows From One Query
I've this sql-query...

$sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']";

The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...

---

Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC;
It generates somewhere around 2.9m rows. I want the last 10:

SELECT expensive query ORDER BY field DESC LIMIT 10

But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of

SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10
We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?

---

SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do
SELECT * FROM case_studies
It outputs all rows.
The query i am usign is:
select programs.program_title,
programs.id, case_studies.id,
case_studies.title, case_studies.author,
case_studies.timestamp from case_studies,
programs WHERE programs.id
= case_studies.id
Any ideas?

---

Query Help, Comparing Rows
Suppose I have the following data:

+----+----------+-----+-----+-----+-----+-----+-----+-----+
| Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
| 11 | 11:20:00 | F | T | T | T | F | F | F |
| 12 | 11:45:00 | F | T | T | T | F | F | F |
| 14 | 12:10:00 | F | T | T | T | F | F | F |
| 15 | 12:35:00 | F | T | T | T | T | T | F |
| 17 | 13:00:00 | F | T | T | T | T | T | T |
| 18 | 13:25:00 | F | T | T | T | T | T | T |
| 19 | 13:50:00 | F | T | T | T | T | T | T |
| 20 | 11:28:00 | F | T | T | T | T | T | F |
| 21 | 11:53:00 | F | T | T | T | T | T | F |
| 22 | 12:18:00 | F | T | T | T | T | T | F |
+----+----------+-----+-----+-----+-----+-----+-----+-----+
I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.

---

How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent...

I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update.

Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.

---

Eliminate Rows In A Query (without Many OR)
This is my query :

SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas

There must be a more efficeint way to find what i want than this :

AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598

Something like cas.id is not (list of values)...

---

Updating Multiple Rows In One Query
tried to find the answer with search but didn't return any answers.

OK, here is the table

table test
------------------------
| test_id | test_order |
------------------------
| 1 | 1 |
------------------------
| 2 | 2 |
------------------------
| 3 | 3 |
------------------------
I'm trying to change the orders in one query, but not sure how to do that.

phpMyAdmin shows me the code like this

Quote:

$sql =
'UPDATE `test` SET `test_order` = &#392;' WHERE `test_id` = 1;'
'UPDATE `test` SET `test_order` = &#393;' WHERE `test_id` = 2;'
'UPDATE `test` SET `test_order` = &#391;' WHERE `test_id` = 3;'
. ' '


I'v tried that but got a syntax error.

MySQL version is 4.0.26, can anyone help please?

---

How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work)

$sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";

---

Query Pulls Out Multiple Rows Even Though Theres Only One
Ive got a query thats selecting info about a product from a table called items and joining on a table called itemimages to get its associated images. The product can have more than one image. If i run the query on an item with 2 images i get 2 results for one item.....when theres only one item.....it seems to duplicate the item for each of its images....

SQL
SELECT items.*, itemimages.* FROM items INNER JOIN itemimages ON (items.itemID = itemimages.itemID)               WHERE categoryID = '$category' AND active = 'yes' LIMIT $start, $limit"

---

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")


Is there any way to do this in one SQL query?

---

How Do I Update Multiple Rows In One Query?
How do I update multiple rows in 1 query? I try not to do a loop of update queries.

---

Average Query With 2 Rows From Same Table
My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?

---

Optimizing Search Query For Millions Of Rows
I have mysql 4.1 and Im having a difficult time optimizing this query.

select domain, length(domain) as len
from domains
where
length(domain) <= &#3916;' and
not (domain regexp '[[:digit:]]') and
domain not like '%-%' and
price > &#390;' and price < &#3916;' and
end > &#55614;&#57159;-12-01'
order by end ASC, len ASC
The following query outputs:


| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
-------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | domains | ALL | end | NULL | NULL | NULL | 2600000 | Extra where; Using filesort |
My indexes are:
ID - PRIMARY, Unique
domain - Unique
end

Is there anyway this query could be optimized anymore? With only 2.6 million rows its taking a 5 or 6 seconds. It looks like its not finding the right keys.

---

Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here

table categories

+--------------------------------------- +
| cat_id | cat_name | cat_total_articles |
+----------------------------------------+
| 1 | PHP | 23 |
+----------------------------------------+
| 2 | MySQL | 17 |
+----------------------------------------+
table articles

+---------------------------- +
| article_id | article_cat_id |
+-----------------------------+
| 1 | 1 |
+-----------------------------+
| 2 | 2 |
+-----------------------------+
Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table.

Is it possible to combine the following queries into one statement?

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles + 1
WHERE cat_id = 2");

PHP

mysql_query("UPDATE categories
SET cat_total_articles = cat_total_articles - 1
WHERE cat_id = 1");

---

Help With Updating Duplicate Rows In Mysql Query.
Quote:

SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC

What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group.

How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution.

I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest.

Also, if possible, I would like it to be done with one query.

---

Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark.
Now i want the result as

Name Raj Rina Tina
Marks 80 90 70

I tried the --vertical option of mysql and G option in the query.

for example when i tried

select *from studentsG

it displayed

*********** 1. row ***********
name: Raj
marks : 80
********** 2. row ************
name: Rina
marks : 90
********** 3. row ************
name: Tina
marks : 70

but i want to display

Name Raj Rina Tina
Marks 80 90 70

---

Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's.

$sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )";
return $sql2;
$res = mysql_query($sql2, $connDB) or die(mysql_error());
$row = mysql_fetch_assoc($res);
based on a test zip code gives a result like


SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 )
the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table.

In my db table lon and lat are decimal(10,6) type with a default value of 0.000000

---

Use Where In Query So It Returns Rows That Has Less Than 10 Characters In The Word
I want to return rows that has less than 10 characters in the word that is used in the where clause sort of thing.

I can't really explain so I will give you an example, which is obviously wrong.

Quote:

mysql_query("select games from gaming where gamename < 10 characters");

---

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

---

Creating Non-existent Rows In Query With Join
I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query:

---

What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this:

SELECT COUNT(*) AS exists FROM mytable WHERE ...
Sometimes I just select the first row and check later in php how many rows have been returned:

SELECT some_col FROM mytable WHERE ... LIMIT 1
But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example:

SELECT id,
name,
(SELECT COUNT(*) FROM mytable WHERE ...) AS exists
FROM othertable
WHERE surname='xxx'
Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.

---

Count One Table's Rows From Multi Table Query
here are my tables (condensed)

FEEDS
feed_id
site_id

SITE
site_id
site_name

ARTICLES
article_id
feed_id
link

I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this:

PHP

$gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles
WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection)
or die(mysql_error());

The query does not use JOIN, ON and all that good stuff.

I just need the following variables to run through a loop:

site_id
the number of articles rows per site_id

---

What's Wrong With My Query To Filter Double Entries And Skip Empty Rows?
I am trying to get filter a database table.
- skip empty rows (i.e. ecardNameSender is empty)
- filter double entries

$sql = "SELECT COUNT(*) as total FROM tblEcards WHERE ecardNameSender != '' GROUP BY ecardEmailFriend";
$result = @mysql_query($sql, $connDB);
$row = mysql_fetch_assoc($result);
$totalPics = $row['total'];
echo $totalPics;
What's wrong with my query?

---

Using A Limit Clause, But Return The Number Of Rows Of The Query Without The Limit
I have heard of a cool feature that mysql provides a way to return the number of rows of an sql statement that contains a LIMIT as if the LIMIT had not been there.

I search the mysql manual, but could not find anything.

---

How To Get The Total Number Of Rows With A Query "limit" ?
I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else?

---

How To Get The Total Number Of Rows With A Query "limit" ?
I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else ?

---

How To Get The Total Number Of Rows With A Query "limit" ?
I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else ?

---