Why does the slow query log show more rows than exist?


Purely a question of curiosity: Why does my slow query log sometimes show more rows examined than the table being hit contains

Code: # 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; Code: mysql> select count(*) from _1_log; +----------+ | count(*) | +----------+ | 111824 | +----------+ 1 row in set (0.00 sec)
Posted On: Thursday 25th of October 2012 11:08:39 PM Total Views:  184
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




What does KEY mean?

I was looking at some SQL statements and I stumbled upon this one that got me confused. Code: CREATE TABLE " . TABLE_PREFIX . "announcementread ( announcementid INT UNSIGNED NOT NULL DEFAULT '0', userid INT UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (announcementid,userid), KEY userid (userid) ) Now, what does the last line "KEY userid (userid)" do I am familiar with primary keys and foreign keys, but what is a key I tried googling the meaning of KEY, but the results usually brings up information on primary keys...
VIEWS ON THIS POST

108

Posted on:

Wednesday 24th October 2012
View Replies!

ORDER BY doesn't sort as expected

Hi Would anyone be able to look at the following statement and tell me why ORDER BY is not behaving as expected. Code: SELECT RTeamName, SUM( RGamePld ) , SUM( RGameWon ) , SUM( RGameLost ) , SUM( RGameDraw ) , SUM( DLegFor ) , SUM( DLegAgainst ) , SUM( RPointsSum ) FROM LeagueTable WHERE RDivision = 'A' GROUP BY RTeamName ORDER BY RPointsSum DESC LIMIT 0 , 30 of the Database fields are correctly spelt and the database has two records in for each team. The results displaying in the SUM() fields is correctly calculated but is not sorting by RPointsSum. The team with the most points is currenty halfway down the table. Any suggestions why this isn't sorting so that the team with two points is at the top of the table Many
VIEWS ON THIS POST

140

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL 4.0.24 doesnt like COUNT(*)?

Seems my Host uses version 4.0.24 and it is fussy about using COUNT(*) if I am reading the error message correctly. Is there another way to make this work for this version Code: LEFT OUTER JOIN ( select Players_Player_ID , count(*) as player_goals from goals group by Players_Player_ID ) as G
VIEWS ON THIS POST

254

Posted on:

Wednesday 24th October 2012
View Replies!

Works, doesn't work - csv file upload

I have had this working and now it doesn't. I am in a fix - behind schedule and promised for tomorrow to client. It worked and now it wont. Respectfully BEG someones help, please. It's a database - problem is I can get a csv file up to the domain but I cannot get it into the database. Says error - cannot connect with usename/password - yet there is nothing wrong with it. To start with I have a test script and it will connect but wont grab a result. If I could get the test script to work I may have a clue! I think it's very simple, but then so must I be... Is there anyone kind enough to offer help, please ************************************ Database has 30 fields, only 1 record for now. Autoincrement field has a value, book_number has a value of 1, author has a value of Johnny-boy >>>>>>>>>>>>>>>>> This script SHOULD grab the author - shouldn't it >>>>>>>>>>>>>>>>> Code:
VIEWS ON THIS POST

142

Posted on:

Wednesday 24th October 2012
View Replies!

What does this MySQL error mean?

I am getting this error. could someone tell me what it means The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
VIEWS ON THIS POST

82

Posted on:

Wednesday 24th October 2012
View Replies!

how long does it take to run the query (was "Mysql help")

if for example i run a query like select * from tablename how can i know how long it takes to run the query.
VIEWS ON THIS POST

81

Posted on:

Wednesday 24th October 2012
View Replies!

What does unsigned/signed/zerofill/binary mean? MySQL manual sucks

I am very frustrated with the MySQL manual, I cant seem to find simple definitions and explanations. Using phpmyadmin and creating tables with fields I don't remember what all the attributes and such things mean really, and when/where/in what cases they should be used. I cant even find a simple definition of what binary, unsigned, signed, and zerofill mean >;( Can anyone help me out Either tell me what they mean and when to use them, or give me a link to where in the fricking manual it says this The manual is so long winded I don't know wtf its talking about. A simple glossary of terms would kick ***...but no.. the online manual has to be a pain in the *** to use. Doh.
VIEWS ON THIS POST

75

Posted on:

Wednesday 24th October 2012
View Replies!

MYSQL doesn't return proper results

I have a strange problem with a v bulletn forum that I run. I'm trying to select all threads that are older than a certain date. I looked up the mysql syntax for the query and got: SELECT * FROM `thread` WHERE dateline < (NOW() - INTERVAL 12 MONTH) I could use any interval and I get the same result of only one record where there isn't any date inserted, just a zero. The database uses a unix time stamp. Trying every variation doesn't seem to return results. In the above query, thread is a table with a bunch of fields and dateline is one of them that has the unix timestamp. I am guessing that perhaps the query doesn't work with unix timestamps and perhaps must be converted into a different format in order to make use of the mysql functions.
VIEWS ON THIS POST

94

Posted on:

Thursday 25th October 2012
View Replies!

EVENT in MYSQL does not run, but seems fine??

Hi everybody, I am struggling to set up an EVENT in mysql - But it doesnt execute the commands, and I have spend hours to figure out why by now. I hope to find the answer here :-) This is the code for the event: Code MySQL: delimiter | CREATE EVENT delete_cart ON SCHEDULE EVERY 5 MINUTE DO BEGIN DECLARE cartID INTEGER( 11 ) ; DECLARE cartProductID INTEGER( 11 ) ; DECLARE cartAntal INTEGER( 11 ) ; DECLARE cartStr VARCHAR( 5 ) ; SELECT id, produkt_id, antal, str INTO cartID, cartProductID, cartAntal, cartStr FROM cart WHERE added_date >= added_date +10000 LIMIT 1; UPDATE products SET stock = stock + cartAntal WHERE produkt_id = cartProductID; UPDATE sizes SET antal = antal + cartAntal WHERE size = cartStr AND product_id = cartProductID; DELETE FROM cart WHERE id = cartID; END | delimiter ; It is pretty straight forward what should happen here. The EVENT is set up and running, but nothing happens. The event_scheduler is set to ON. I just cant see why Has the variables been declared with a wrong syntax Hope to get an answer on this soon,
VIEWS ON THIS POST

90

Posted on:

Thursday 25th October 2012
View Replies!

MySQL query does not return desired results

Hi Guys! I have the following SQL query and it's not returning the deed results. I think it has something to do with the where clause (in particular the l.postcode field). Here is some data that I have inside the jobs_locations table: Code: 1010 1001 CM14 Brentwood, Essex 1010 1001 IG7 Chigwell, Essex 1010 1001 SE2 London 1010 1001 TW8 Brentford, Middlesex Given the data the SQL query should return the second row but it doesn't. Any ideas Code: SELECT jobs.job_title, jobs.job_url, jobs.job_salary, jobs.job_ote, jobs.job_perks, jobs.job_description, jobs.date_posted, companies.company_name, companies.company_url, companies.logo_thumbnail, jobs_industries.industry_id, jobs_job_types.job_type_id, count( applications.id ) AS applications, count( featured_jobs.id ) AS featured, l.display_name, l.postcode FROM jobs INNER JOIN companies ON companies.id = jobs.company_id LEFT OUTER JOIN applications ON applications.job_id = jobs.id LEFT OUTER JOIN featured_jobs ON featured_jobs.job_id = jobs.id INNER JOIN jobs_industries ON jobs_industries.job_id = jobs.id INNER JOIN jobs_job_types ON jobs_job_types.job_id = jobs.id INNER JOIN ( SELECT job_id, if( count( job_id ) >1, 'Locations throughout the UK', display_name ) AS display_name, postcode FROM jobs_locations GROUP BY job_id ) AS l ON l.job_id = jobs.id WHERE jobs.status = 'on' AND l.postcode = 'IG7' GROUP BY jobs.id ORDER BY featured DESC , date_posted DESC
VIEWS ON THIS POST

65

Posted on:

Thursday 25th October 2012
View Replies!

How come myPHPadmin doesn't make use of deleted row numbers?

I just did a practice insert that put in 1700 rows. When I verified that it worked, I deleted all of them. The next rID that was used, on the next insert, was still 1700 higher than I thought it should be. Won't mySQL make use of primary row numbers that have been deleted Otherwise I can see that primary key field getting quite high over time. Thank you. , Originally Posted by busboy Otherwise I can see that primary key field getting quite high over time. let's say you use INTEGER UNSIGNED and insert 10 rows per second any idea how long it will be before you run out of numbers spoiler: more than 10 years
VIEWS ON THIS POST

213

Posted on:

Thursday 25th October 2012
View Replies!

mysql privilege: does "select" allow me to join

After I changed my privileges to just select, this query ceased to function: Code SQL: SELECT base_SalesTable.* ,buckets.width ,buckets.teeth FROM base_SalesTable LEFT OUTER JOIN buckets ON base_SalesTable.name = buckets.name WHERE base_SalesTable.category = :category AND base_SalesTable.mincap = :mincap ORDER BY weight Happy Holidays
VIEWS ON THIS POST

153

Posted on:

Thursday 25th October 2012
View Replies!

UNION ALL doesn`t return all available data

Hi , in below query I tried to joint some subqueries with "UNION ALL" operator to fetch some data in two columns (pack & warehouse) : Code MySQL: SELECT ft.leafCode ,ft.leafName ,ft.colorsCode ,ft.pack ,ft.warehouse FROM (SELECT rs_leaves.leafCode ,rs_leaves.leafName ,rs_colors.colorsCode ,COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.receivedNUM Else Null End), 0) - COALESCE(Sum(CASE WHEN sent_receive_tbl.stationID = '7' THEN sent_receive_tbl.sentNum Else Null End), 0) As `pack` ,NULL AS `warehouse` FROM sent_receive_tbl INNER JOIN rs_leaves ON sent_receive_tbl.leafID = rs_leaves.leafID INNER JOIN rs_colors ON sent_receive_tbl.colorsID = rs_colors.colorsID Group By rs_leaves.leafCode, rs_leaves.leafName, rs_colors.colorsCode UNION ALL SELECT tt.leafCode ,tt.leafName ,tt.colorsCode ,NULL AS `pack` ,tt.warehouse FROM (SELECT st.leafCode ,st.leafName ,st.colorsCode ,NULL AS `pack` ,COALESCE(Sum(st.INPUT), 0) - COALESCE(Sum(st.OUTPUT), 0) As `warehouse` FROM (SELECT rs_leaves.leafCode ,rs_leaves.leafName ,rs_colors.colorsCode ,NULL As `INPUT` ,Sum(assign_details.assAmount) As `OUTPUT` FROM assign_details INNER JOIN rs_leaves ON rs_leaves.leafID = assign_details.leafName INNER JOIN rs_colors ON rs_colors.colorsID = assign_details.leafColor Group By rs_leaves.leafCode, rs_colors.colorsCode UNION ALL SELECT rs_leaves.leafCode ,rs_leaves.leafName ,rs_colors.colorsCode ,Sum(receipt_details.recAmount) As `INPUT` ,NULL As `OUTPUT` FROM receipt_details INNER JOIN rs_leaves ON rs_leaves.leafID = receipt_details.leafName INNER JOIN rs_colors ON rs_colors.colorsID = receipt_details.leafColor Group By rs_leaves.leafCode, rs_colors.colorsCode ) As st Group By st.leafCode,st.leafName,st.colorsCode ) AS tt ) AS ft Group By ft.leafCode ,ft.leafName ,ft.colorsCode; In output I get NULL values as warehouse output but it is not valid : +----------+----------+------------+------+-----------+ | leafCode | leafName | colorsCode | pack | warehouse | +----------+----------+------------+------+-----------+ | A | Stone1 | 1014 | -5 | NULL | | B | Stone2 | 1020 | 50 | NULL | | B | Stone2 | 2000 | 345 | NULL | | C | Stone3 | 1032 | 68 | NULL | | F | Blur3 | 1014 | -6 | NULL | | I | Wood3 | 1032 | 215 | NULL | +----------+----------+------------+------+-----------+ How can I take actual value for warehouse These are some descriptions to confirm that warehouse shouldn`t drive NULL value : mysql> select * from receipt_details; +-----------+-------+----------+----------+-----------+-----------+ | detailsID | recID | leafName | leafCode | leafColor | recAmount | +-----------+-------+----------+----------+-----------+-----------+ | 1 | 1 | 1 | A | 1 | 200 | | 2 | 1 | 2 | B | 3 | 112 | | 3 | 1 | 9 | I | 2 | 30 | | 4 | 2 | 9 | I | 2 | 36 | | 5 | 2 | 3 | C | 2 | 34 | | 6 | 6 | 2 | B | 5 | 8 | | 7 | 22 | 6 | F | 1 | 80 | +-----------+-------+----------+----------+-----------+-----------+ mysql> select * from assign_details; +-----------+-------+-----------+----------+----------+-----------+-----------+ | detailsID | assID | projectID | leafName | leafCode | leafColor | assAmount | +-----------+-------+-----------+----------+----------+-----------+-----------+ | 1 | 1 | 1 | 1 | A | 1 | 20 | | 2 | 1 | 1 | 2 | B | 3 | 8 | | 3 | 2 | 2 | 3 | C | 2 | 4 | | 4 | 2 | 2 | 9 | I | 2 | 6 | | 5 | 2 | 1 | 6 | F | 1 | 30 | | 6 | 3 | 3 | 2 | B | 5 | 12 | | 7 | 3 | 1 | 2 | B | 3 | 5 | | 8 | 1 | 1 | 6 | F | 1 | 20 | | 9 | 3 | 1 | 1 | A | 1 | 15 | +-----------+-------+-----------+----------+----------+-----------+-----------+
VIEWS ON THIS POST

97

Posted on:

Thursday 25th October 2012
View Replies!

What does @ mean?

A really elementary question, but what does the @ sign mean in a query I'm trying to number each row in the query results and all the help online for MySQL shows this kind of thing: Code SQL: SELECT @rownum := @rownum +1 AS COUNT but I don't know what the symbol means and I can't seem to find an explanation in either my book or online.
VIEWS ON THIS POST

67

Posted on:

Thursday 25th October 2012
View Replies!

What does Archiving Means ?

, I sometimes think what does archiving of data should do If i have data in a table and i want to archive some data what I should really be doing which will mean that data is archived and what are its benefits 1. Should i just change the status of the record from active to archived 2. Should i move the archived data to another table , database or db server What are we really trying to achieve by archiving the data
VIEWS ON THIS POST

53

Posted on:

Thursday 25th October 2012
View Replies!

LIKE does not work properly

I have set up a page with the following MySQL query: SELECT cd_id, cd_number, cd_title FROM cds WHERE cd_title LIKE '%$search%' ORDER BY cd_number ASC This finds some of the search terms that are there to be found, but others it does not find. Can anyone tell me why please
VIEWS ON THIS POST

139

Posted on:

Thursday 25th October 2012
View Replies!

Unable to save result set , normal repair doesnt work

Something has gone wrong here, and I dont know what. mysql5 , big table with many hundreds of tables. In a period of only a few hours, many errors start to pop up on my site. It's always like this Code: Warning: mysql_query() [function.mysql-query]: Unable to save result set in... But it is happening in more then 20 different tables, most of them unrelated to eachother. I've done a few 'myisamchk -r *' on them, but it doesnt seem to help. When I look at the tables in phpmyadmin, some give me a warning that the table is marked as crashed, and that I should repair them. But that warning stays, even after the above repair command. Some others look fine, but when I perfom specific where/limitby queries it sometimes still gives the 'Unable to save result' error. I have no idea what to do now. I've had a few minor problems with corrupted tables before, but it was always in one table at a time, and it could always be solved with myisamchk -r. At worst, I lost a handfull of rows, but nothing more. But now a whole lot more is going wrong, and I dont know what to do now. On a few tables, I tried replacing them with 24hr old backup files, and after the chown/chgrp/chmdd on those files, it seems to work. But I'd rather not replace my whole database with a backup yet. Are there any tips on what to check or what to do now could it be a hardware problem
VIEWS ON THIS POST

136

Posted on:

Thursday 25th October 2012
View Replies!

EXISTS subquery doesn't work

My SQL is like this: Code: DELETE FROM table_a a WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE b.id=a.related_id) It doesn't work, I'm using XAMPP with MySQL version 5.0.33, what version is it supported
VIEWS ON THIS POST

74

Posted on:

Thursday 25th October 2012
View Replies!

int(M) in mysql - What does M mean?

Just wonder, what means have number M at the field declaration like field_name INT(M) I have used to use it as length in bytes, mannual claim, that M indicates the maximum display width for integer types , but simple tests doesn't show any difference between int(1), int(3), int(5), int(11). So basically, what does that number mean
VIEWS ON THIS POST

305

Posted on:

Thursday 25th October 2012
View Replies!

Table doesn't exist ERROR

, I'm getting a "Table doesn't exist" error. The name of the table is "sitesearch" But the table does exist! Strange... So I executed : Code mysql: SHOW TABLES ...and used PHP var_dump() to output the array fetched. The var_dump() gave me this : Code text: array(10) { [0]=> array(2) { [0]=> string(10) "categories" ["Tables_in_funny_main"]=> string(10) "categories" } [1]=> array(2) { [0]=> string(8) "comments" ["Tables_in_funny_main"]=> string(8) "comments" } [2]=> array(2) { [0]=> string(10) "commercial" ["Tables_in_funny_main"]=> string(10) "commercial" } [3]=> array(2) { [0]=> string(8) "features" ["Tables_in_funny_main"]=> string(8) "features" } [4]=> array(2) { [0]=> string(5) "files" ["Tables_in_funny_main"]=> string(5) "files" } [5]=> array(2) { [0]=> string(5) "geoip" ["Tables_in_funny_main"]=> string(5) "geoip" } [6]=> array(2) { [0]=> string(7) "members" ["Tables_in_funny_main"]=> string(7) "members" } [7]=> array(2) { [0]=> string(7) "traffic" ["Tables_in_funny_main"]=> string(7) "traffic" } [8]=> array(2) { [0]=> string(10) "traffic2ip" ["Tables_in_funny_main"]=> string(10) "traffic2ip" } [9]=> array(2) { [0]=> string(5) "votes" ["Tables_in_funny_main"]=> string(5) "votes" } } So it doesn't show up with the SHOW TABLES query. Fact is...the table does exist! I created it and I can see it in phpMyAdmin. I have also restarted the MySQL server with no change. Any ideas why this could be happening
VIEWS ON THIS POST

68

Posted on:

Thursday 25th October 2012
View Replies!