Most Efficient Way To Create Drop Down Boxes With Item Count Using MySQL
[Back Type: All]
[Hardback (100)]
[softback (400)]
and
[Genre: All]
[Biography (94)]
[Crime (244)]
[Horror (14)]
[Romance (148)]
View Complete Forum Thread with Replies
Related Forum Messages:
Side Menu Item Invisible Unless MySQL Contains Item
I have a menu attached to a MySQL DB that has various items stored and are called with PHP when a user clicks on the link. (I included the menu code below) I want to add a menu item that is only visible if there is something stored in the DB. I bolded the area below as to the part of the menu I want the field. Can someone please show me how to incorporate this in that general area with PHP? I have scoured the Web and Googled but could not find a way to do this. Code:
View Replies !
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 Replies !
How To Create A Query To Get Top 5 Item For Each Date
I have running out of idea on hw to create a query to get each day top 5 item with a date range given more than 1 month or more. meant: if i have a date range from 2006-01-23 to 2006-10-23, then i want to get the latest 31 days data only with each day TOP 5 item. How? Lets say i have a table with this keyword: MyDate;Item;CountItem; The date range is unknown and can be any range selected by user. I am trying to think of put auto increament for each day with CountItem desc and each day the column of auto-increament is set to 5 only so at last i can just get all the data from it. But the question is i dunno how to do it? Anyone have better solution? I know if using store procedure then can do this but is it can done without store prodedure?
View Replies !
Selecting Item List And Count From Other Table
I have been failing at trying to get this query to work. Here is the query I can't get to work: SELECT cont.key_no, ( SELECT COUNT(1) FROM items itm WHERE itm.cont_key_no = cont.key_no ) AS item_count FROM containers cont WHERE cont.status = 'OPEN'; Here is a query I managed to get working, but it doesn't quite do what I want it to. It doesn't return a container and item count for containers that don't have items: SELECT cont.key_no, COUNT(itm.key_no) AS item_count FROM containers cont, items itm WHERE cont.status = 'OPEN' AND itm.cont_key_no = cont.key_no GROUP BY cont.key_no Here are what the tables look like: containers ---------- key_no status items --------- key_no cont_key_no If you can't tell from the examples above, I am trying to return a list of all 'OPEN' containers and how many items are in the container, even if no items are in the container.
View Replies !
Different MySQL Boxes
Hopefully someone might have insight to something for me. I'm a newbie and have a situation. I did a project with a "given" environment of MySQL 5.1. The final destination client has 4.1 on a different box. We've tried taking the files from 5.1, copied to other machine and tried to read from them and found out it can't, do to the file pagine format, or whatever that I read about.... Anyhow, they are incompatible formats. Aside from a full rebuild of all the data, is there a shortcut to get the data transferred? One thing I was thinking of, and heard similar capabilities from a SQL-Server is to have a connection to another database and then do a select from 5.1 database and create a table into the OTHER database directly.... This instead of a full select into OUTFILE delimieted, then doing a LOAD DATA INFILE..
View Replies !
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
View Replies !
Can NOT Create Or Drop A Database
I just Installed MySQL v4.1.14 and had created a Database (from a script). But I would like to redo the Database portion of it but am not able to drop the Database. If I do a "show database;" I still see the database. But when I look in the data directory, it is not there. Below is my attempt the create / drop the DB: mysql> create database testlink; ERROR 1007 (HY000): Can't create database 'testlink'; database exists mysql> drop database testlink; ERROR 1008 (HY000): Can't drop database 'testlink'; database doesn't exist
View Replies !
Rights For Create And Drop
i`m working with mysql on a suse linux server. i am not root on the server and for working in mysql a got a user called "student". the rights were given to me with GRANT ALL PRIVILEGES ON dbname.* TO 'student'@'localhost' GRANT ALL PRIVILEGES ON dbname.* TO 'student' identified by 'password' but i am not able to do creates or drops on the db. can this have to do with me not being root on the machine or what was done wrong? / how should this be done correctly?
View Replies !
Mysqlbinlog :: No Log For Create Or Drop Table?
I have a problem with MYSQLBINLOG.EXE: result file doesn't contain log such as CREATE TABLE and DROP, even if bin files contains it correctly. This is a problem because I use bin result file to manage update of my (test) application, and when I create an Index, or modify a table, exporting bin log files I can manage it simply. But, and I don't know what is changed, results file contain only data change, and nothing about table change contained in the bin file. There are some ideas on what I can try?
View Replies !
USE After DROP And CREATE (script In Query Browser)
I'm writing a simple script that creates the database and tables: ------------------------------------ DROP DATABASE IF EXISTS mydb; CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_unicode_ci; /*workaround: use mysql; */ use mydb; DROP TABLE IF EXISTS movie; CREATE TABLE movie ( id int not null auto_increment primary key ); ------------------------------------ When executing, Query Browser says "No database selected (1046)" on lines with table names. I add USE <dbname> after its creation, but it only works if the previous default database is different. A work-around is to add "USE mysql" just before the real USE, but it's ugly. Any solutions?
View Replies !
Can't Create/Edit/Drop Specific Table
had a table named "lines". All of a sudden this morning, I could not access this table from the cli. Everything I tried, even a simple "DESC lines;" returned: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lines' at line 1 I was however able to access and edit the table via phpmyadmin. I dropped the table via phpmyadmin, and tried re-creating it via the CLI, but get the same error messages. I also tried creating a brand new database, and creating a "lines" table in it, and I also get the same error. So I can't create table called "lines" in any db, new or old, at the moment. I may have a view involving this table, if that may cause problems, however a SHOW VIEWS also returns the above error message. Mysql Ver: 5.0.32-Debian_7etch4-log
View Replies !
Selecting List Boxes
I am wondering if there is an easy way for php to automatically reselect an item from a list box. The purpose is this: The user will be filling out a form which, before it gets sent to mysql, I want them to double check what they typed in. I am doing this all in one php page. So the user clicks "submit" and it brings them back to the same form asking them to please double check. Everything then is entered in the form already. Witht he text fields I just set the initial value to be it vvalue in the $_POST array. However, I am not quite sure how to do this with the lsit boxes.
View Replies !
Rights To Create Table, Select, Then Drop Table..
I have a need to get data from the db that requires me to=20 1) do a select and create a new table with the results=20 2) run a query against that new table=20 3) drop the new table=20 I have a script on my server that does this using the root account that has all on *.* for the db. It works fine.=20 I now want to get these results on a web page.=20 I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done.=20 what priviledges do I need to give that user?=20 currently I have the following but the user can't even log into the db from the command line..=20 mysql> show grants for user;=20 +----------------------------------------------------------------------- ---------+=20 | Grants for user@% |=20 +----------------------------------------------------------------------- ---------+=20 | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '6fe4c0ab2cf30ae3' |=20 | GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' |=20 +----------------------------------------------------------------------- ---------+=20 2 rows in set (0.00 sec)=20 when I do a "show grants for user", what should I see to allow what I want?
View Replies !
Problem With Drop Index And Drop Column
I have a table named employees and a table named chargeaccount. Employees has a foreign key with chargeaccount called chargeaccountid. All I want to do is remove that foreign key column. The index name is fk_chargeaccount_employees But I have a problem with this query: alter table employees drop index fk_chargeaccount_employees and this one too: ALTER TABLE `timesheet`.`employees` DROP COLUMN `ChargeAccountID`;
View Replies !
Is This An Efficient MySQL Setup?
I would like to offload the MySQL server from my dedicated box in order to speed up page loads. I don't have an additional dedicated server so my only option is to get a VPS. But is offloading the MySQL server to a VPS, albeit a modest one, even worthwhile? I understand this is a very broad question because I am not providing any details but that being the case I am expecting a broad answer
View Replies !
Most Efficient, Stable Version Of MYSQL?
We're still using mysql 3.23.56, so it's time to upgrade! It has never once crashed on us, so we've been content with it so far. However, some of the inefficient query optimizations make us want to upgrade. Does anyone know what the current most efficient and stable version of MYSQL is? We don't require views or cursors or stored procedures or any of that... Just whatever's the most reliable and best optimized for fast query execution of fairly simple SQL. I know that 3.23 has some issues with using indices correctly during optimization (most notably in ORDER BY ... DESC). Is that problem fixed in 4?
View Replies !
How To Create Complex Query With Group By And Count
I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: ......
View Replies !
Mysql And Drop Down List
Is it possible to have a drop down list (1-22) that will exclude a number if it has a corresponding field in a database where the value is either open or closed. ( it is a sports tipping script)Ie: if round 1 tips have been picked and it is now round 2 then can the digit (1) be removed from the drop down list so a user cannot choose round 1.
View Replies !
Mysql Drop Index
Why does mysql drop index very very slow in a large table? I have a large table with more than 5M rows, and many indexes. Now I want to drop some of them. But it seems that mysql can not handle this very well. It takes a very long time (more than half an hour) to do , and make me unbearable.
View Replies !
Item=item+1
I have the following query going: $query = mysql_query("UPDATE sites SET hitcount=hitcount+1 WHERE siteid='".$siteid."' "); but instead of adding 1, it adds 2, every time. I'm on MySQL 4.1.18. What am I doing wrong?
View Replies !
Item Before Another
I need to put an item before another and they are ordered by Id which is an autoincrement column, how do i do it? mysql_query("REPLACE table WHERE a = $a SET id = $newid ...then? how do i tell mysql to add a 1 to all the other id which are greater then the one i just updated?
View Replies !
Put Item First
Hello, i have a basic (?) problem, i have a long list of data sorted: abcdefghijklmn I'd like to take one 'f' and move in front: fabcdeghijklmn And do that over and over How do i do that in a way i can traverse the list, item by item. Do I have to do a linked list (risking pointer trouble etc)? I'm using PHP and Mysql, the data will be maybe 0.5 - 10 million rows.
View Replies !
SELECT Item
I can't seem to get this although I know it's probably simple. I need to SELECT the text in a specific col, but just the text next to the largest auto_increment number.Code: id | col_text _______|__________ 1 | text A 2 | text B 3 | text C 4 | text D ... ... etc The thing is, I won't know what the actual number is in the id col, i need a way to select the text in the record with the largest id number.
View Replies !
Related Item
im currently working on a simple lyric page: I have 2 data : title and keyword when a user view a particular lyric, i want a related lyric by title & keyword to be display example data in database title Green Day Walking Alone Keywords(separated by commas) Green Day, walking alone, tabs, midi i use this code below but the result display only one title which is the same title as the currently viewed lyric SELECT title, keywords FROM lyrics WHERE title LIKE '%".$arr[title]."%' AND keywords LIKE '%".$arr[keywords]."%' how to fix this is my sql format correct ?
View Replies !
Setting Up New Item
I use myphpadmin and I am a beginner with it. I have this text based game and for items it's weapon, armor and shield. I went to insert and did the fields and added a new item called "Herb" which heals your character 10 health. So now I go to the game to the admin control panel and its there but it can only be a weapon, armor or shield. I need to add a new class for "items" which is a healing item.
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 !
Item With Multiple CatIDs
I have just started a new database, and in it I have a table of items. One of the fields is 'categories' which relates to another table of category IDs, but some items will have more than one category ID. Whats the best way of doing this? And how do I select data? eg. SELECT all items WHERE catID = 4 SELECT all items GROUP BY catID SELECT item 56 WITH category data.
View Replies !
Returning Top 5 Tags For An Item
System Info: RedHat Linux 9 (text only) Mysql 3 When I execute mysql from the command line I receive an error: "error 2002: Mysql cannot connect to socket /var/lib/mysql.sock" (or something similar) - the socket was definately not "/tmp/mysql.sock" It was installed by the RedHat 9 installer. Do I have to edit some configuration file.
View Replies !
How To Search All Fields For An Item?
I currently use a query like: SELECT * FROM sr_main WHERE footprint LIKE 'conn%0p80%'; What I would like to be able to do is search ALL record fields rather than searching just the field 'footprint'. There must be an easy way!? I thought this would work: SELECT * FROM sr_main WHERE * LIKE 'conn%0p80%'; but it seems I can't wildcard the fields... is that right?
View Replies !
Apply SHA() To Every Item In Column?
I have a password column with the passwords already set for every user. Is there a simple statement I can use to apply the SHA() encryption function to every value in the password column in my table users?
View Replies !
Inserting An Item Into 2 Tables
I have a simple database with 2 tables. Table 1 = Product Info, and Table 2 = Product Stats. I could have it all in one table, but I use two tables to keep things organized. I have a unique ID that ties the two tables together. Now, I have a little problem adding a new product to my database (with a php script). This is what I have: mysql_query("INSERT INTO products(name,description,price,size,weight) VALUES(''$name','$desc','$price','$size','$weight') ")or die(mysql_error());mysql_query("INSERT INTO stats(popularity,sales,opinions) VALUES('','','') ")or die(mysql_error()); The problem is that sometimes when I add a new product, the ID will be different in each table. For example, I just added a new product, and the ID in table "products" was 114 while the ID in table "stats" was 115. This of course causes problems, each product has to have the same ID on both tables. Is there a better query I could use? The products table always has info inserted, but the stats tables starts-off with all values being empty (they get populated with time, as sales start, etc)
View Replies !
Select Items With Same Catehory As Given Item
I have two tables: TABLE_ARTICLE article_id******article_name article_content TABLE_ARTICLE_SECTION article_id *section_id Given an article_id, how do I select all articles from TABLE_ARTICLE that have the same section_id as that of a given article_id. Something like this but for mysql: SELECT table_article.* FROM table_article,table_section WHERE table_section.section_id IN (SELECT section_id FROM table_section WHERE article_id="something")
View Replies !
Select More Than One Item From Column In Table
I'm trying to select a few items in a table. Select * from table where column1 = 'a' and 'b'; I have a feeling its either the '=' or the 'and' bit at the end of the query statement. I've tried the 'or' and 'like' and running out of ideas and the will to live.
View Replies !
Displaying Item In Favourite List
i have save user's favourite posting in to database.. the record is saved in the format "1,3, 15" when user adding a new posting id (eg.10) to the favourite list,the record become "10, 1,3, 15" i'm having problemin displaying the favourite list of the user. how can i display the item in the below format? id:10 title: author: id:1 title: author: id:3 title: author:
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 !
Table Design For Storing Item Stats
I've created quite the database for my app, and I'm familiar with normalization and general db design. However, I'm ready to design my tables for recording all sorts of stats for my db items. I want to be able to view stats for each item, such as number of small views, number of large views, number of downloads, ratings and so on. I also want to be able to view these items by periods of time, such as by day, week, month, by multiple months, by year, etc. So, I'm trying to figure out what the most efficient way of recording this data, so that I can keep my tables as small as possible. I'm wondering if I will have to insert a row for every stat, or if I should enter a row for every day, or only a day if there is activity, etc.
View Replies !
Browsing Records: (first, Prev, Next, Last) And Getting A List # Like Item # Of ##
Very simple database to store comics, I'm trying to start small with PHP and MySQL. table name: comics Fields: ID: int auto-increment primary-key title: tinytext date: date location: text (stores the relative file location, "images/comics/1.jpg") So now my trouble... In a perfect world, the database ID field would one day have 100 records with ID numbers 1-100 consecutively. In the real world I see myself having to delete entries for whatever reason which will leave gaps in the ID field. Is there a mysql command to go to the next entry that comes after a certain ID number? Same with previous. Is there a way to find the highest number ID in the database quickly? The lowest? Finally, I would like to display something such as "Comic X of XX". XX is easy, I can just use count. But if I retrieve ID 22, how do I know how many records came before it without doing something like looping through the database in PHP and running query for each preceeding ID#? I assume that PHP loop method would be time consuming and a poor use of resources? Or would that actually make sense? Sorry for the newb questions. The book I bought was more reference than tutorial, I didn't have enough time to pick one out properly which leaves me floundering a little with the basics.
View Replies !
Selecting All Items From Table1 Where It Does Not Have An Associated Item In Table2
I have a table, menu_items, with id, service_parameter, and text. I have another table, menu_item_parents, with parent_id and child_id, so that an item might have multiple parents and multiple children. Now I want to select all top level items. It seemed like it'd be simple enough, but I can't figure it out. How owuld you select all items in menu_items that does not have any rows in menu_item_parents where child_id = the menu item id?
View Replies !
|
TRACKER
