Efficient Way To Count Rows
I'm trying to get the number of rows in a table with a very large number of records in it (~9 million). When I run a select count(*) for some criteria (where name='something', etc) it takes around 6-8 secs for the query to return the value. I tried by using SQL_CALC_FOUND_ROWS with a very small LIMIT but then the query was taking even longer.
I'm using InnoDB, with query caching enabled. I could look at the information_schema and get the approximate row count but whenever I use a where clause it'll be way off mark.
View Complete Forum Thread with Replies
Related Forum Messages:
Efficient Ways To Retrieve Specific Rows.
My site used forum software that I wrote myself (in Perl) which, up until now, has used flat files. This worked fine, however lately I've been wanting to do more stuff with user accounts, and had been eying MySQL for over a year. Finally I've decided to start off small by converting the forum's account system to a MySQL database (and convert the rest later after I'm comfortable). So far everything is working fine, and I've figured out how to create the table, insert records, modify records, and so on. However I had a question on what was the most efficient way to retrieve information about a user as I read through the flat file containing messages. As each message is read I want to find that user's relevant information, build it into my output, then continue on to the next message. Now here's my problem. Since I'm reading a flat file in a way that was once trying to be somewhat memory efficient (on really slow server way back when, trying to avoid arrays and hashes) I'm finding that I'm having to send separate questions to MySQL as each message comes up. Say from 1 to 20 very simple questions to complete printing the page. Also, sometimes the questions might be repetitive - since I'm not storing any of the results in memory, if a user appears twice I ask about it twice. Would I really be better off trying to find a way to consolidate all distinct users into a single question? Or is MySQL efficient enough that this isn't really much of a concern?
View Replies !
The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people The table is quite large (300,000 rows) I need to write code that update many of the rows in the table Here is what I need to do Two of the fields that I store about each person are: location and country The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements I cannot use LOAD DATA as this is product database Is there a way to update multiple rows (each with a different value and condition in a single query?
View Replies !
Count Rows For Each Hour The Last 24 Hours, Including The Hours With Zero Rows
I have a table for logging log-in's with these columns: id, datetime, name. I want to see all the logins the last 24 hours for a specific name. I want mysql to return one row per hour (24 rows) and how many logins this name has done in these hours. Example result: 2009-02-13 00:00 2 2009-02-13 01:00 0 2009-02-13 02:00 0 2009-02-13 03:00 1 2009-02-13 04:00 0 2009-02-13 05:00 3 2009-02-13 06:00 5 2009-02-13 07:00 0 etc... 2009-02-13 23:00 7 I made this query: ......
View Replies !
Count The Rows
I've built quite a few large join statements, which cross-reference 6 or 7 tables each. I'm using LIMIT to allow for pagination, which works fine. However, I need to find out how many rows were returned in total, and don't fancy throwing in another join to count() the results with some where statements. Anyway I can quickly find out what would've been returned without the LIMIT?
View Replies !
Count(*) Sum Of Rows
i have several columns, with hundreds of rows of numerical data in the db. i'm wanting to set it up to where when i SELECT the data from the data base it doesn't show me hundreds of rows of numbers. I want it to add up the data for each column and display their totals only. I've tried using count(*) and * , count(*) as times, and i keep getting errors. what do I need to do here's the code: MySQL Code: $info_query = mysql_query("SELECT * FROM Report_Listing_Daily WHERE listing_id = '$listing_id' AND MONTH(day) = '$month' AND YEAR(day) = '$year'");while ($row = mysql_fetch_array($info_query)) {echo "Summary: ".$row['times'];echo "Detail: ".$row['detail_view'];echo "Click Through: ".$row['click_thru'];echo "Email: ".$row['email_sent'];echo "Phone: ".$row['phone_view'];echo "Fax: ".$row['fax_view'];}
View Replies !
Count Identical Rows
I've got a log table looking like this:Code: user | action | time | | a | click | 2005-05-05 a | login | 2005-04-05 b | logout | 2005-03-02 b | click | 2005-01-25 And now I wanna display the statistics in a neat interface. But I'm having problems... is there a way to select from the above table and get rows like this:Code: user | click | login | logout | | | a | 56 | 23 | 45 b | 45 | 2 | 0 c | 12 | 32 | 32 Perhaps this is more than I could expect from MySQL?
View Replies !
To Count All Rows With The Same Value On One Column
I have a table that contains a few thousand rows and what I want to pull back is the number of rows that each have the same number in one column. Basically, pull back all rows and then display a total for each seminar happening. I can join this table to tx_seminars_seminars that has a list of all seminars available but I can't work out how to get a total for each different seminar happening... My SQL is: SELECT COUNT(*) FROM tx_seminars_attendances AS seatCount WHERE seatCount.seminar=NEEDS TO PULL A LIST OFALL SEMINARS IN ONE BY ONE AND seatCount.deleted<>1 AND seatCount.user<>3991 AND seatCount.user<>3992 AND seatCount.user<>4006 AND seatCount.dateDeclined IS NULL AND seatCount.dateRemovalApproved IS NULL AND seatCount.dateCancelled IS NULL AND seatCount.dateAuthAbsence IS NULL AND dateSubmitted IS NOT NULL
View Replies !
Count Same Field In Rows
i need to calculate the number of the same IP address that appear in the database, how can i do that? example of the database: IP Address Date and Time 127.0.0.1 5/23/2007 127.0.0.1 5/24/2007 192.168.1.2 5/25/2007 192.168.1.5 5/26/2007 127.0.0.1 5/27/2007
View Replies !
Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null. So here is my problem: I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?
View Replies !
Count() Rows With 0 Matches
I am using the code below to select all the 'centres' in the database that have 'sessions' today. SELECT c.*, r.region, COUNT(*) AS count FROM centres c, regions r, sessions st WHERE c.c_id=st.c_id AND st.session_date=CURDATE() AND c.region_id=r.region_id This works and the results might look like: centre / count centre1 / 20 centre2 / 10 centre3 / 3 However, is there a way to get MySQL to also select centres from the database that have no matches in the sessions table? So the results will look like this: centre / count centre1 / 20 centre2 / 10 centre3 / 3 centre4 / 0 centre5 / 0 centre6 / 0
View Replies !
Count Similar Rows
I have a table where every search made on a site is logged. The table columns are id, text, uid, time and ip, text being the string of text that was searched. I need to take the text column and somehow conclude what the most popular searches are. Does anyone know an efficient way to do this? Obviously, I could just select all the rows and use php to figure out the most popular searches, but this doesn't seem very efficient.
View Replies !
Count Distinct Rows
If I have a table like this: col1 col2 1 100 2 100 3 300 4 200 5 100 And run a query like this (dont know if it is right syntax): SELECT DISTINCT(col2) * FROM Table I would get something like this: 1 100 3 300 4 200 But I would like to get the count of each distinct row like this: 1 100 3 3 300 1 4 200 1
View Replies !
Count Fields Over Multiple Rows
I have a database like this id, field1,field2,field3,field4,field5 Database contains 100 rows, some rows have no fields filled, some 1field , some 2 fields etc. How would i count the number of fields filled in total? So the outcome is (number of fields filled in row1)+(number of fields filled in row2)+(number of fields filled in row3)....................+(number of fields filled in row100)
View Replies !
Count How Many Matching Rows Exsist
I am new to SQL Querries, can someone help me figure out how to get a result? I have a column in my db called; emails some of the email addresses in this column may contain similar domains: person1@site.com person2@site.com person3@someothersite.net I need the result to count and end up being 2 not 3 Not sure how to make the sql statement: COUNT (*) FROM database_tabe WHERE email='$email' LIKE '%@%.%' Not sure how to get this result...
View Replies !
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?
View Replies !
Select Count(*) Showing -1 Rows.
In one of my preliminary tests with mysql, the below statement is showing the following way. What does -1 rows signify in the table with the select count(*) query. Is this (-1 rows in the query result) an error condition to be taken care ?. bash-2.03# ./mysql -u test Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 to server version: 5.0.18-max Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Code:
View Replies !
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?
View Replies !
Count Number Of Rows Returned?
this is my connect and everything code: <?php // connect to the mysql database server. mysql_connect ($dbhost, $dbusername, $dbuserpass); mysql_select_db($dbname) or die(mysql_error()); $query = "SELECT DISTINCT category FROM $tuttable"; $result = mysql_query($query) or die(mysql_error()); while ($row = mysql_fetch_array($result)) { echo ($row['category']." | "); } echo count($row); ?> Notice "echo count($row);" How do i get this to work properly. It only returns one, because there is only 1 that it is working on. I want to return the count of all the rows that fit that query.
View Replies !
How To Get Count Of Rows Returned While Using Group & Having?
i'm using the having keyword to find certain set of rows and they are working properly. but what i want is , i want to count the total number of rows returned by this query using mysql. select name,count(date) from emp group by date having count(date)>3; this returned returns all the names that have more that 3 entries in the same date. it returned 5 rows and how can i get the count of rows(5) the query returned .
View Replies !
Count Difference Between Dates In Table Rows
There's a table with event logs. Every record in this table is a code a an event and time when the event occured. What I need is to find event which occured in a longer time before following event than a specified interval of time. Example table: Event Time 3 2008-08-08 14:00:00 3 2008-08-08 14:00:20 3 2008-08-08 14:00:40 3 2008-08-08 14:21:10 Interval: 20 s Let's say it's now 14:21:20, then the event I'm looking for is on the row 3 because there's more than 20s gap between times in row 3 and 4 and row 3 is the last such row. If it was 14:22:50, then the event I'm looking for is on the row 4 because there's more than 20s gap between now and the last row and row 4 is the row with the highest date.
View Replies !
The Fastest Way To Count Rows In Innodb Tables
As count(*) doesn´t work so fast with Innodb like it works with Myisam what is the best and fastest way to count rows in Innodb? I use indexes in my Innodb tables so maybe select indexed column query with mysql_num_rows could be the fastest way or at least faster than count(*)!?
View Replies !
Limit Results And Count Total Rows?
I have my database set up with 3 tables: POSTS, TAGS, and TAGMAP. Here is an example: TABLE "posts" id title content time -- -------- ----------- ---- 12 my blog title 1 bla bla bla... 1218826136 13 my blog title 2 bla bla bla... 1218826136 14 my blog title 3 bla bla bla... 1218826136 TABLE "tags" id name -- ----- 45 cars 46 trucks 47 motorcycles TABLE "tagmap" id tag_id post_id -- ------ ------- 156 46 12 157 46 14 158 45 12 159 47 13
View Replies !
COUNT And GROUP BY Display All Counted Rows
have a table as follows ID | ORG | Subject ----------------------- 100 | 14 | Some text 101 | 18 | Text1 102 | 18 | Text3 103 | 14 | Text4 104 | 18 | More text What query would I need to get the following output? (when I use COUNT(ID) and GROUP BY ORG it only returns 1 ID per ORG) ORG 14 - 2 results 100 103 ORG 18 - 3 results 101 102 104
View Replies !
Return A Count Of Number Of Rows Before Desired Row.
Anyone know how? find_in_set doesn't seem to be able to do what I want since I am not searching through an array but am actually running a query. What I really want is to be able to select all until something = something. I've searched the realm of google but cannot come up with anything useful, and so I am wondering if maybe I don't know the right words to be looking for.
View Replies !
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
View Replies !
Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being PHP SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT FROM grps RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid) GROUP BY grps_c.catid ORDER BY grps_c.title which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add PHP LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ') however adding that removes the rows that have a 'count' or NULL or Zero.
View Replies !
What's The Most Efficient Way?
I've done a database scheme and wonder what the most efficient way of storing certain entries would be. The site will have news, reviews and tutorials and these will be under the same categories. So I'm wondering what the best way to do design the database scheme would be. Have one table like I've done now in the "content" table with the posibilty to differentiate the entries with the "post_type" field or have three different tables? Below is the table "content". Field Type Null Default post_id int(10) No post_date datetime No 0000-00-00 00:00:00 post_text text No post_title text No post_cat_id int(4) No 0 post_description text No post_name varchar(200) No post_type int(4) No 0
View Replies !
How Efficient Is In() , And How Much Is The Most That You Should Put In An In()?
I am just wondering as to the efficiency of the in() function in MySQL. select field1,field2,...,fieldn from table where id in(1,2,3,4,5,6, ... , n); assuming: id is an indexed field. table is a VERY big table (100 000+ - 1 million+ records) what do you think is the largest number of values you could pass to the in() function without completly flattening your server?
View Replies !
Most Efficient YES/NO...
I) Table_A has fields Data_1, Data_2, Data_3, and Data_4; I need to determine whether value 'X' is present (at least once) in any of the Data_N fields. All I need is a YES/NO answer: YES, 'X' is present (at least once) in one of the Data_N fields of Table_A [Record number(s) not required!], or NO, 'X' is not present in any of the Data_N fields in any of the records in Table_A. II) Similar scenario, but this time Table_A has fields RecID, Data_1, Data_2, Data_3, and Data_4. I need to determine the RecID (none, one, or more) of every record that has value 'X' in any of the Data_N fields. I'm looking for the query that will be fastest and most elegant to implement.
View Replies !
Quickest And Most Efficient
i run an online game. I want to give each user a ranking, based on how high thier score is.If for example, i had 20,000 players, i dont want to have to update them all one by one, it may strain the server and take a long time. Is there another way i can assign a rank number (rank 1 has the top score and so on)
View Replies !
Efficient UPDATE
I have a table with the following structure; CREATE TABLE my_table ( id_1 int(11) NOT NULL , id_2 int(10) NOT NULL , stauts tinyint(1) NOT NULL DEFAULT 0 , PRIMARY KEY (id_1) ) Engine =InnoDB'; The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query; UPDATE IGNORE my_table SET id_1 = 74240, id_2 = 5 I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.
View Replies !
Efficient Tables.
I am starting out on a project where I need to store GPS information. The data consists largely of a series of "Points" each consisting of a longitude,latitude and elevation. On a typical "route" there could be hundreds of points. My question is how can I efficiently store this information. It does not sound sensible to normalise this and add hundreds of rows to a table for each "route". Sample data is along the lines of : - <trkpt lat="54.016942977905273" lon="-1.4903640747070313"> <ele>82.330078125</ele> <time>2006-09-03T07:35:41Z</time> </trkpt> - <trkpt lat="54.016938870772719" lon="-1.490332055836916"> <ele>0</ele> <time>2006-09-03T07:35:42Z</time> </trkpt>
View Replies !
Efficient Storage Of IP Address
I am establishing a database for the purpose of logging access to my secure webserver and am wanting to make the database as efficient as I can because it will be doing a lot of work when the site goes live. What is the most efficient way in a MySQL table to store remote IP addresses? What data type should I use? Should I just go with a basic VARCHAR(15) to allow for 4 sets of 3 digits with 3 decimal separators, or is there a better way?
View Replies !
Efficient Placement Of Fields
is there any noticable efficiency is ordering the position of the field types?. Like say i place the join keys in a table at the end, and varchars and text at the top of the table does it really matter? Also when i do a query like select * from table where id IN (1), where 1 is usually a primary key int is that quicker than doing where id=1 or is there no difference and is it handling the int as a string or as an int?
View Replies !
Efficient Database Structure
I have been developing a new website and i need help in deciding the best database structure for it. The site is basically a dating website with various modules like blogs, videos, comments, friends, photos etc. I have created a member table that stores all the basic profile info and created seperate tables for friends, photos, messages, blogs etc and MemberID as foreign key. Now on profile page, i want to display all info related to member like his profile info, his photos, friend list, messages etc and i have to execute 7-10 short queries on profile page for this. Also, i think Joins will not be much helpful as there is one to many relationship e.g there can be more than 1 photo for a member and i am saving each photo in a seperate record. Similar is the case for other tables?
View Replies !
Creating An Efficient Database
i'm currently writing a web based catalogue system in php using a mysql database. the catalogue has a number of products in it from different brands. i would like to know if it is more efficient to have each brand in a separate table then a "master" table just listing the brand name and corresponding table or all the products in one large table and a "master" table listing each brand in the large table. the large product table would of course have a field to state which brand the product was from. the efficiency would be based on users being able to access the database via html browsers using php and also search the database.
View Replies !
How To Make Index Most Efficient
I have a large table (> 3,000,000 records). Each records contain a primary key like 'id' and a lots of attributes like 'age', 'department'. I want to build some indices to accelerate my query. I read the document which says that too many indices may slow down the INSERT and UPDATE operations. So is there any rule on how to set indices in such table? If I create an index for every field, would that be a very bad idea? If I create an index on each of two fields but not on their combination, will the indices contribute to queries on the combination?
View Replies !
Efficient Way Of Mass Indexing?
I have been working on a program that will populate and index a database. The populating doesn't take too long, but the indexing does. My question is: Is there a better approach to indexing this table than the one I'm using right now? I'm doing this through QtSql. headers is a QString array of 48 column names I want to index. for (int i = 0; i < headers.size(); ++i) { q.exec("ALTER TABLE mysqltable ADD INDEX(" + headers[i] + ");"); } example run for 20000 rows: viper,david $ time ./main -r 20000 Database Connection Established 0.520u 0.060s 0:21.27 2.7% 0+0k 0+0io 0pf+0w
View Replies !
Is This INSERT With SELECT As Efficient As Possible?
I'm writing a pretty complex web app and will be repeating many times over a query very similar to below and need to know if there is a more efficient way to do it. If anyone has input, I'd be happy to hear: INSERT INTO table (somecolumn) VALUES ((SELECT id FROM other_table WHERE foo = 'bar'))
View Replies !
|
TRACKER
