Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The
thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.
I am using mysql jdbc driver and not sure if there
is any API that deals with the physical size of the resultset.
resultset.getFetchSize() talks in terms of rows v/s the actual space
they take.
View Complete Forum Thread with Replies
Related Forum Messages:
Determine Index Size
I am trying to determine how I may determine how much space each index is taking? I am using Mysql v4.0.14, and my .myi file is 1112785920, and I have 6 indexes currently. I need to add two more, and am trying to estimate how much the MYI file will expand.
View Replies !
How To Determine The Index Size
I must search on a tinytext field. I want to put an index on it to make it faster. but i have to specify the size for that index. MySql says this: "....Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters...." how do i calculate the number of bytes needed to get a good index on a tinytext field knowing that smaller is better? My Character set is in UTF-8.
View Replies !
Selecting Rows While Using Values From These Rows In The Where Clause
I have a table which includes 3 columns: id: just an id name: a name parent: the id of the row which this row is a child of. In my example I want to select the rows with the id 5, it's parent, and the parent's parent. I found a syntax that workes in this case: SELECT `name`,`id` FROM `locations` WHERE `id`='5' or `id`=(SELECT `parent` FROM `locations` WHERE `id`='5') or `id`=(SELECT `parent` FROM `locations` WHERE `id`=(SELECT `parent` FROM `locations` WHERE `id`='5')) However my question now is if this syntax could be shortened while still working, and also if there is a way to allow for rows to be selected that do not have a parent( value 0 in my table) or that only have a parent but no grandparent.
View Replies !
Rows Based On First Letter
I'm trying to figure out the fastest/best way to return rows based on the first letter of the title column in my table. For example, I want to get all the article titles that start with the letter "a". Is there any difference between the two select statements? Any reason to use one over the other? Memory? Speed? SELECT title FROM table WHERE LEFT(title, 1) ='a'; or SELECT title FROM table WHERE title like 'a%';
View Replies !
Filtering The Rows Based On Its Timestamp
I'm trying to write a backup script that takes the last modified rows of a table in x days. I can do it if I have a timestamp column for every table in the database, but my aim is to write a script for general use as anyone can use it with his/her database without the need to add that column. This will be possible if there a property gives the last modified timestamp of a row. does anybody has any way to reach that target?
View Replies !
Size Of DB - Do Too Many Rows Cause A Problem?
What is big for a MYSQL DB. E.g. I am working on someone elses DB that has a many-many link table that notes what emails are sent out to what users. Therefore each time a message gets sent out from this application, if it got sent to 500 users for example, 500 rows would be added to this table. If the amount of users was something like 10,000 then each time they were all mailed this would be 10,000 rows added. It is already at 250,000 and as the new maintainer of the DB should this be someething I should worry about?
View Replies !
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
View Replies !
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag: resource - 1:M - tagged_resource - M:1 - tag So a resource can have many tags and a tag can belong to many resources. The data would look like this: RESOURCE TABLE id: 1 name: resource1 TAG TABLE id: 1 tag: new id: 2 tag: tested TAGGED_RESOURCE resourceID: 1 tagID: 1 resourceID: 1 tagID: 2 How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE? I have tried using 'IN' but it acts like an OR operator rather than AND.
View Replies !
Size Of Rows With Repeated Values
I'm storing an inverted index on a MySQL database. This index stores a mapping from words id's to the location of that words in a set of documents: CREATE TABLE `i_index` ( `id_word` MEDIUMINT NOT NULL , `id_text` MEDIUMINT NOT NULL , `position` SMALLINT NOT NULL , ) so if word 1 appears on text 2 on positions 3 and 4, we will have the rows: {id_word,id_text,position}:{1,2,3},{1,2,4} Therefore, most rows will contain duplicated values for both id_word and id_text. My question is: Will each row require 8bytes (2xMEDIUMINT + 1xSMALLINT) regardless of the fact that only `position` is actually changing, or will MySQL somehow use a data structure which doesn't store repeated information to save disk space?
View Replies !
How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is: CREATE TABLE `big` ( `keyval` int(10) unsigned zerofill NOT NULL, `name` varchar(20) default NULL, `avail` enum('y','n') default NULL, PRIMARY KEY (`keyval`), KEY `keyval` (`keyval`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.
View Replies !
Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem: Simplified tables: ARCADES ======= ID, name GAMES ====== ID, name ARCADES_GAMES ============= ID, arcade_iD Games_ID Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those. I can do soemthing like... SELECT a.name FROM arcades a WHERE EXISTS(SELECT 1 FROM arcades_games WHERE arcade_id=a.id AND game_id IN (11,14)) But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14. It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?
View Replies !
Select Rows From Date Not Time In Where Clause
I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working
View Replies !
Delete Rows From Multiple Tables (AND & WHERE Clause)
I have a database containing the folllowing tables; PUPIL id ... ROA pupil_id ... MESSAGE id pupil_id ... REPLY message_id ... How can I delete all the rows from all tables containing the same pupil id and all replies to messages from the same pupil? I managed it using AND clauses in WHERE but if there is no data in say the MESSAGE table then it doesn't work.
View Replies !
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.
View Replies !
Applying A WHERE Clause To Several Rows On A Joined Table To Select A Single Row
I have one table which is users for example id | username and another table called ratings for example user_id | title | rating and I want to select users who have rated more than one thing... I can do this easily if I want to select users who have rated just one title using the following query SQL Code: SELECT * FROM users INNER JOIN ratings ON users.id = ratings.user_id WHERE ratings.title="blah" GROUP BY users.id but cannot think of a way to do this for more than one rating. Doing SQL Code: SELECT * FROM users INNER JOIN ratings ON users.id = ratings.user_id WHERE ratings.title="blah" AND ratings.title="blahblah" GROUP BY users.id
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
Size Of Indexes Versus Data File Size
I am adding indexes to a table with about 200,000 records. Every field indexed adds about 2MB to the index file (*.MYI). The index file is around 8MB and the data file is around 10MB. Is there some optimum ratio I need to know about? (I have examined the queries and am indexing field used in WHERE, ORDER BY and GROUP BY clauses.)
View Replies !
Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000. I have need to use a reference in the outer from clause in a subquery in the select clause. Consider the following example: Select (select b.baitID from b where b.entrydate curdate()) as wantedBaitIDs from bait_tbl b; My actual need is more complex than this as part of it is a rough cross tab. If I try to define the table in the alias, not only do I lose whatever benefits there are in the particular join I would use in the outer from clause but would also require the join to be defined in each subquery, requiring it to be examined each time it is used. To be absolutely clear, in this example I want to use bait_tbl with the alias of b in the subquery. In my actual query I reference the same table twice with a different join set for each. I need to reference a particular alias as that has the join set I need.
View Replies !
Determine Previous Month
basically I need to display a list of "unpaid" statements for the previous Month, no matter what day the search/request is made on. I know MySQL has the DATE_SUB function where I can subtract 1 Month from the current date, but will that subtract the date exactly 1 month to the day ? Or will it simply give me the Month as I need ? What happens if the search takes place on the 31st of a month (for example: July), yet the previous month only has 30 days (June). Will it roll back to the previous month correctly or think that the date is 31st June or try to roll back further until it finds another month which has 31 days ?
View Replies !
Determine Replication Status
I have configured a master/slave replication setup. The idea is only to allow changes made to the master databases. However, I would like to know is there a way to tell there are changes made to slave which in turn breaks the replication.
View Replies !
Determine Latitude Longitude
So I have two tables. One table contains information about waypoints that are to appear along a hiking trail. One of the columns in this table is "miles_hiked". This column should be used to determine where along the trail this waypoint should be placed. The second table describes the trail and consists of closely spaced points from the beginning of the trail to the end and has columns latitude, longitude, mile_marker. What I would like is a query that results in a table with columns for the latitude and longitude for each waypoint. These lats and longs should be found by taking the 'miles_hiked" for each waypoint and finding the corresponding closest "mile_marker" from the trail table. In other words, if one of the waypoints has a miles_hiked of 15.0 miles, then the following query would return one row with the lat and long of that way point: select t.lat, t.long, t.mile_marker from trail t where t.mile_marker >15.0 limit 1
View Replies !
Code To Determine Date?
ive been using code to extract info from a database at the end of each week, and it works fine. the only issue is i have to go in at the end of every week and change the dates before i run the code, i would love to have it automated so that it would pick up that i needed the info from the past 7 days... heres the code that im using SELECT (SUM(t1.timespent) /60) , (SUM(t1.timebillable) /60 ) , MAX(t4.title) , FROM_UNIXTIME(t1.dateline) FROM swtickettimetrack AS t1 LEFT JOIN swtickets AS t2 ON (t1.ticketid = t2.ticketid) LEFT JOIN swusers AS t3 ON (t2.userid = t3.userid) LEFT JOIN swusergroups AS t4 ON (t3.usergroupid = t4.usergroupid) LEFT JOIN swstaff AS t5 ON (t1.forstaffid = t5.staffid) WHERE t1.dateline >= UNIX_TIMESTAMP('2008-07-01') and t1.dateline <= UNIX_TIMESTAMP('2008-07-31') AND t4.title<>'' GROUP BY t4.usergroupid
View Replies !
Determine University Server
I am trying to add some php scripts to show some information to my students in my website which is in the university server. Now definitely this needs to be done using mysql to keep track of the database, but i am not so sure if its installed in the university server. Even if it is I don't know if I can access it or not. I can go and ask the web administrator on monday about it, but is there any other way to figure out if I can create a database in my server and use it without having to wait till monday.
View Replies !
Determine DELETE Success
I want to know if I successfully deleted a row. Right now I first see if the row exists then delete it but I can probably get the success code from the DELETE itself can't I ? $id=100; $query="DELETE FROM mytable WHERE id=$id"; mysql_query($query); ...did it work, was there a row with id=100 ? How do I know?
View Replies !
How To Determine If Transaction Has Been Started?
I am wondering if there is a way to determine if a transaction has been started? For example: BEGIN [way to determine if transaction has been started returns 1] COMMIT [way to determine if transaction has been started returns 0] As you can see, this functionality that I am looking for will determine whether the BEGIN call has been called since session start or since the last COMMIT or ROLLBACK. Anyone know if this available?
View Replies !
Group By, Determine Which Record To Show
I have the following table with data Code: Answer Id | User Id | Answer | Date_Created 75 1 earliest 2009-01-01 75 1 middle 2009-01-02 75 1 latest 2009-01-03 76 1 earliest 2009-01-01 76 1 middle 2009-01-02 76 1 latest 2009-01-03 76 2 earliest 2009-01-02 76 2 middle 2009-01-03 76 2 latest 2009-01-04 75 2 earliest 2009-01-02 75 2 middle 2009-01-03 75 2 latest 2009-01-04 I'm trying to pull the last answer (determined by the date) each user has on record for each question. So my ideal data for this data set would be.....
View Replies !
How To Determine Which Port MySQL Is Running On (LocalHost)?
I tried running "mysqladmin variables -u root -p" and seeing what it lists, the only things that seemed relevant are: | hostname | MyComputer (valid machine name) | named_pipe | ON | port | 0 | report_port | 3306 | report_user | Is there anything else I should get? Is there anyway to confirm which MY.INI it is loading? Why is PORT 0? And what is REPORT_PORT? When I start the MySQL database and check the DataMachineName.err file I see the following: 090327 22:06:45 InnoDB: Started; log sequence number 0 46409 090327 22:06:45 [Note] Event Scheduler: Loaded 0 events 090327 22:06:45 [Note] H:DevelMySQLMySQL Server 5.1inmysqld: ready for connections. Version: '5.1.32-community' socket: '' port: 0 MySQL Community Server (GPL) I assume this means it is running on PORT 0? How does that make any sense? Some of the things I've already tired (maybe it will help you guys figure out what my next step should be): C:>telnet localhost 3306 Connecting To localhost...Could not open connection to the host, on port 3306: Connect failed C:>telnet localhost 0 Connecting To localhost...Could not open connection to the host, on port 0: Connect failed MySQL runs fine from the command line And the User is "root" with password "pass" Service is running (started it manually - and console application works) mysqladmin.exe -u root -p ping Enter password: ** mysqld is alive I added BINMySQLd.exe to the firewall options @ opened port 3306 on the Firewall - didn't touch my router (using localhost - I imagine I don't need to go that far). Running it in PHP5 always gives me the following error: $this->connection_id = @mysql_connect('localhost', 'root', 'pass'); >> Can't connect to MySQL server on 'localhost' (10061)
View Replies !
Using Count(*) With Distinct To Determine Multiple Copies
I have an sql table 'messages' that stores peoples messages, in the form below: (Cname = conversation name) User Recipient Cname Subject Message Fred Bill Weather Rain Levels I think they are low Fred Harry Traffic Congestion Its getting worse Fred Bill Traffic Congestion Its getting worse Fred Harry Money Savings The interest rates dropped Fred Bill Money Savi
View Replies !
Using A Query To Determine The Maximum Value A Number Col Can Hold?
I'm trying to find out how can you determine the largest value you can enter into a column of type smallint,mediumint,int, etc using a query. I know what the values are by looking at the documentation, but I would like to get this number using a query at runtime. So when I insert values into different tables/columns I can use an appropiate restriction befor trying to insert.
View Replies !
How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set? I would like to pass the calculated offset into the limit half of and ordered select statement. E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23. To do that I need to find the offset of photo with id=23 in select id, datetime, caption from photos order by datetime; Then I could get the result I want by doing.... select id, datetime, caption from photos order by datetime limit $offset, 5; I've spent several hours scouring around and found some people with similar problems, but no solutions yet.
View Replies !
Next And Previous Buttons MySQL Rows Displaying 3 Rows Per Page
I have got a table "members" and the DB name is "jutland". I have got columns - id, name, surname & email. I want to display 100 records with 5 rows per page using PHP going back and forth with previous(previous 5 rows) and next (next 5 rows) links. I have the knowledge of the LIMIT x, y condition but still stuck on passing required parameters.
View Replies !
Any Way To Optimize Join To Find Rows Without Conditional Foreign Rows?
I've got a table of keywords that I regularly refresh against a remote search API, and I have another table that gets a row each each time I refresh one of the keywords. I use this table to block multiple processes from stepping on each other and refreshing the same keyword, as well as stat collection. So when I spin up my program, it queries for all the keywords that don't have a request currently in process, and don't have a successful one within the last 15 mins, or whatever the interval is. All was working fine for awhile, but now the keywords_requests table has almost 2 million rows in it and things are bogging down badly. I've got indexes on almost every column in the keywords_requests table, but to no avail. I'm logging slow queries and this one is taking forever, as you can see. What can I do? # Query_time: 20 Lock_time: 0 Rows_sent: 568 Rows_examined: 1826718 .....
View Replies !
Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below: 1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)? 2. How can I insert the correct id’s as implied below? I have tried just about everything (including “Insert …select”, “LAST_UPDATE_ID();”, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message; I would greatlyappreciate someone who could instruct me on how to accomplish this task.
View Replies !
Using A Web Based Database
My web site host allows me to create MySQL databases on its database server. I've created a database as a trial. Now I would want users of my website to supply data to the database and for me to be able to extract it for analysis.
View Replies !
Web-based Chat
I'm debating between 2 different table setups for a web-based chat system (gchat clone). The first is just one table labeled 'chat' which has the columns 'user_1' 'user_2' 'time' 'message'. The second would be dynamically creating/deleting tables with the user names as table names like 'user_1/user_2'. I don't know a whole lot about DB table organization but I was thinking the actual 'select' statement would be faster with seperate tables. Any ideas?
View Replies !
|
TRACKER
