ORDER BY W/ HEAP Table Type
I am creating an online application that lists the last 50 users that loaded a page.
I used a InnoDB table type for storing most information (which rarely changes), but was thinking of moving the UPDATE and SELECT function that tracks who last clicked something to a HEAP table for speed.
I think I would be OK with the UPDATE to a HEAP table, but I have read that ORDER BY cannot be used on HEAP indexes. But, do I have to use an Index?
Can I make a SELECT like this on a HEAP table that has only two columns (login and datenow)?
$queryonlineusers="SELECT login FROM mmih_users ORDER BY datenow DESC LIMIT 50";
View Complete Forum Thread with Replies
Related Forum Messages:
Flush A Heap Table To Disk
For example if I have data that I want to be inserted in to a heap table (for optimisation reasons) but I don't want to risk losing *all* the data if mysql crashes. Is it possible to perform something like a flush so that if mysql does crash some of the data is saved.
View Replies !
Flush A Heap Table To Disk?
As the subject says, is it possible to flush the contents of a heap table to disk? For example if I have data that I want to be inserted in to a heap table (for optimisation reasons) but I don't want to risk losing *all* the data if mysql crashes. Is it possible to perform something like a flush so that if mysql does crash some of the data is saved.
View Replies !
Order Issues With Int Column Type
I have a table with a primary int column type auto_increment. I then have a script that relies on the int column within the table to have the inserted rows in the correct order. The problem is, all of the inserted rows will get the auto_increment id and it will be fine for awhile, and then random rows will begin to be out of order number wise. It is key for the script to have all of the inserted rows in order, but for some reason MySQL keeps messing it up. So I have to consistently perform the query: ALTER TABLE `table1` ORDER BY `postid` The query works, but I'm not always on when the table starts to mess up so it is very inconvenient to have to do this over and over again. By the way I'm running version 5.0.27-standard-log.
View Replies !
HEAP So Slow
I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns... ID | Title title is indexed. When i run a query like this ------------------ SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds... ------------------ Even a single like statement just on `index` (heap) takes 3-6 seconds. Here's the table stats... ---------------------------- Data 397,442 KB Index 24,639 KB Total 422,081 KB ---------------------------- Why??
View Replies !
Heap Vs InnoDB Vs MyIsam
I'm trying to improve performance of our website, the boss is complaining that its too slow (though it is faster than a lot of sites). We experience particular slow down during updates - which are done periodically throughout the day. We have nearly 2 million records that are being searched and theres a lot of information there to pull back. Currently all the tables are myIsam, and the total size is around 1.5Gb but the tables being searched probably total 600Mb. We're currently using all 2GB memory at high loads. I tried converting the main search table to InnoDB but that just made everything horrendously slow - I also heard that it limits to 200 inserts per second whereas with myIsam we're getting around 1k - 1.5k per second. Could the slow down be due to not converting the linked tables to innoDB? Or just lack of caching? I now HEAP is supposed to be super fast for selects, how is it for inserts? Do you think 600Mb database would be ok to be loaded into 2Gb memory and still cope with high traffic loads (relatively high - approx 500 simultaneous users) Or should I stick with MyIsam?
View Replies !
Speed Up Order Table Table-name Order By Col-name For Large Table?
I have a table with 2 column: id and name. Column name is of type varchar(100). The table has about 0.1 billion rows, taking 11G disk space. Now what I want to do is alter the table by the name column. I ran the command: "order table table-name order by name" 1).Firstly, it create tmp table,which takes 19G disk space. 2).After that,the 19G space seems to be released(I see the free disk space increase by "df"). 3).Then I see that in the mysql directory, the size of the mysql file #sql-14f7-2.MYD" increases 2M every time, but very slow. I guess this mysql file is the new table after order. But how can it be so slow? It only increase 2M every 2 minutes or so, which would takes weeks to finish the command. So could any one tell me how can I speed up this command? Are there any variables I can adjust to make this faster?
View Replies !
Re-order The Natural Order Of All Of All The Rows Of A Table
Basically I want to re-order the rows of my entire database. It has many thousands of rows, and people are constantly running the same query against it. The problem is its quite slow (using a shared host). I know you can use an sql query to order by column, however I need to re-order twice and it really seems to be slow due to this. Since the query is always the same, if the rows were already ordered in the database , then the ordering query wouldn’t need to be done. Is there an easy way to do this, without deleting each and every row, then inserting them again? basically in the database I have: The natural order when you do a select * from this table (without using an order by query): ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |dataX |dataY |dataZ | |datal |datam |datan | (obviously a lot bigger than this) and I want reorder the entire table , eg The natural order when you do a select * from this table: ________________________ |cola |col.. |coln | |dataA |dataB |dataC | |datal |datam |datan | |dataX |dataY |dataZ | So is there an easy way to do this, am I just being dumb? or do I need to delete the entire table and insert the rows one at a time in order?
View Replies !
Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie. *** SEE ATTACHED IMAGE PLEASE I have tried all sorts of SQL statements, e.g: SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC Nothing seems to work. I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ? I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.
View Replies !
Create A New Table With Table Type??
I was creating a new table via a query and when i went back and looked at the size, it was over 3MB! This with only five records in it. Here's what I found: table size(bytes) type A 26 MyISAM B 66 MyISAM C 0 MyISAM D 3276 InnoDB E 0 MyISAM so then i tried to create the table defining the type but it keeps erroring out on me. My question is: How/Can you create a table with the TYPE defined via a query? create table testtbl (yr varchar(4)) type=myisam <- works create table testtbl as (select * from table1) type=myisam <- errors
View Replies !
Table Type And Precautions
I am beginning to create a medical database and a user frontend for physicians to be able to enter patient data.. practically an Electronic Medical Record. What would be the best setup regarding security for mysql. Is mysql even a good choice for this type of application? InnoDB tables vs. MyISAM vs. something else? Obviously redundancy is a huge deal that I will take much precaution with. Any scripts or techniques I could use to ensure easy mysql backups? Anything else I may be neglecting?
View Replies !
Matrix Type Table
Tables in MySQL have columns that are individualy named. Suppose your data consists of a large matrix. You don't want to name each column, suppose you have a million columns. So the question is: How do you use CREATE to set up a table that contains a large matrix?
View Replies !
Column Type In Table
I want to add a column in my database table to store a collection of integers like {1,3,4,5}. does anyone know what the datatype for this column should be?
View Replies !
Table Type InnoDB
I am struggling a bit to get going with mysql and my tutorial works through InnoDB tables without a clear explanation of the setup. To date I have modified the /etc/my.cnf file. mysqld starts without complaining. I have modified my.cnf to include: innodb_data_file_path = /data/mysqldata/innodb_data/innodata:10M:autoextend innodb_data_home_dir= It turns out that logged in as a mysql user if I "use innodta' it mounts as a the database. So am I correct in assuming that identifying the tablespace name in innodb_data_file_path is the equivalent of 'create database <databaseName> with a MyISAM table? Also once having 'use innodata' I can issue a create tables command and then see <tablename>.frm .MYD, and MYI listed in the innodb_data directory. However when <tblename> was created I could not use the 'engine = innodb; ' as the last line of the create table sql statement without getting an error. This suggests that the table that I made without 'engine=innodb' is just the usual MyISAM based table. Right? Wrong? Huh? I think the documentation is a little lacking in this area as are two other MySQL books that I purchased
View Replies !
Alter Table Type
I am trying to alter my table type so I can use a foreign key. I am using this command and it says it is updated, but the table type doesn't atually change. Do I need to recreate my tables, starting out with this type of table? ALTER TABLE employees TYPE = innodb; Even when I creae a table, it doesn't give me the option of what kind of table type. I am using phpmyadmin to manage my DB. EDIT: I just did some research and found that my service provider doesn't offer innodb as an option. Is it still possible to set up my tables with a foreign key to link my various tables together?
View Replies !
Creating Table Type Innodb
I am trying to create tables with type innodb. I created a database and all the tables of type ISAM. Now I am at the point of making relations and establish refrential integrity. For that ofcourse I have to change the tables in the Innodb type. I did that with the commad "alter table tablename type = innodb". Command runs fine but the table type doesnt change. I tried to create a new table of type innodb but its not working either. I dont know what the problem is. Do we have any command to enable the innodb property or what?
View Replies !
What Type Is Each Table? (MyISAM Or InnoDB)
I'm have about 200 MySQL databases on my server. Each database has 114 tables. All of the tables SHOULD be InnoDB, but I've found a few that are MyISAM. I'm trying to write a perl script to give me a list of all the MyISAM tables. Is there any sort of select statement that will give me the table type? (MyISAM or InnoDB) The only way I can figure this out is doing a 'SHOW CREATE TABLE' on each table and then looking at the table type from there. It would be 1000 times easier if I knew a select statement that would give me just the table type.
View Replies !
Excluding A Certain Column Type In A Table...
I have a quick and dirty question here, here's my current query for MySQL through PHP: SELECT * FROM `cms_pages` WHERE ".$query_body . " ORDER BY `page_title`; Where i want to change this because I am storing also some administrative mysqllates within there that show up in the results... Which yes it's all protected by user rights and all, but I want to "exclude" a certain group as such on result time: Title Account > Contact Information Account > Register Administration » Manage » Rewrites » Create Rewrite Administration » Manage » Rewrites » Edit Rewrite Terms and Conditions As you see above the Administration type pages is the ones I want to remove from the search results, where I'm trying to find the "MySQL query" that will basically say, "Exclude anything that says Administration." Basically I have a column 'theme_name` that outlines the Administration part under as 'admin' which I think if there is some sort of exclusion syntax I can use within my query in MySQL, what would it be to say something like this: SELECT * FROM `cms_pages` WHERE ".$query_body . " EXCLUDE * FROM `theme_name` LIKE %admin%, ORDER BY `page_title`; How can this be done properly inside MySQL? So me being new to MySQL and find this the appropriate forums for MySQL queries, I do appreciate any and all help. Because I can't for the life of me figure out how to query the DB Table and add in an exclusion type part to my MySQL query...
View Replies !
Default Table Type On MySQL 5
I seem to recall reading somewhere that InnoDB was the default table type for MySQL 5. Yet when I did a 'show table types' it indicated that MyISAM was the default type since version 3.
View Replies !
Best Table Layout For This Type Of Data?
I have a database of products, say computer hardware. The products table has all products because most products have the same stuff, price, name, model, etc...but some products have "extra" stuff. Say, if it were computer products a hard drive might have a speed or size associated with it but a monitor would not. Would it be best to add say like 5 columns(spec1,spec2,spec3) and they just contain the data if it exists or is there another way like create a hardrive table with (speed, size) columns and link the product table to that table?
View Replies !
Alter Table Type MyISAM To INNODB
I've got a table with ~250k rows. When I try to alter it's type from MyISAM to INNODB, it just sit's on 'copying to tmp table' for an obscene length of time, until I eventually kill it. I've done it to a few 150k row tables with no problem. Takes about 5-10 seconds. I then found another table with a similar row count, was able to alter it without a problem. But it was smaller in size. So found one bigger, with double the rows, and 10mb larger.... And still, it worked.
View Replies !
Convert Table Type From InnoDB To MyISAM
I've writing my site's search engine - and it currently used InnoDB for two of the tables that I need to search - primarily the assets table. This table has a couple of FKs. Now I'm thinking that using FULL TEXT indexes might be the better way to search these tables - but I can't switch them to MyISAM without getting an error : cannot delete or update a parent row - foreign key constraint fails. The interesting thing is that I had another table with a foreign key that I was able to switch to MyISAM. So, is the FULL TEXT search worth it? Is this really the best way for writing my advanced search engine. I think that it probably is. However, I can't convert my table to MyISAM. So, what's the work around for this problem?
View Replies !
Alter Table Type: MyISAM To INNO DB
I've got a table with ~250k rows. When I try to alter it's type from MyISAM to INNODB, it just sit's on 'copying to tmp table' for an obscene length of time, until I eventually kill it. I've done it to a few 150k row tables with no problem. Takes about 5-10 seconds. I then found another table with a similar row count, was able to alter it without a problem. But it was smaller in size. So found one bigger, with double the rows, and 10mb larger.... And still, it worked. Does anyone have any ideas as to why this one table might be inclined to be so beligerant?
View Replies !
Modify Table Engine Type Failing.
I keep getting the following error when trying to change the tables engine type. Why is this failing and how can I fix it? mysql> ALTER TABLE trade_messages ENGINE = INNODB; Query OK, 827793 rows affected, 1 warning (1 min 3.90 sec) Records: 827793 Duplicates: 0 Warnings: 0 mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1266 | Using storage engine MyISAM for table 'trade_messages' | +---------+------+--------------------------------------------------------+ 1 row in set (0.00 sec)
View Replies !
ALTER TABLE `table` ORDER BY `column_a` DESC
I try to get better performance by implementing this: http://dev.mysql.com/doc/mysql/en/alter-table.html "ORDER BY allows you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later." I run this SQL query on one of the table:
View Replies !
How To Model Variable Number/type Table Columns?
I currently model reports using a single report table with it's fixed set of columns describing the parameters of a report. Now we'd like to make the system more flexible by introducing a number of report mysqllates. Each mysqllate defines a different set of parameters a report can have, and the user gets to decide what report format they want by assigning it a mysqllate. the problem is that if I include all those possible parameters as columns in the report table it will be inefficient. No report uses all of them, so every row will only use a subset of columns to store values. I thought the solution would be to create a new table called report_field, and then have a many:many relationship between it and the report table. When a user selects a mysqllate to build a report, I'd create a row in the report table, N report field rows based on what the mysqllate dictates, and then link them. But the problem is that the parameters can be of varying data types, and I can only specify the data types of the columns in the report_field table when I create the table. I could create report_field_int, report_field_date tables etc. But that doesn't seem the right way to go. Querying becomes very difficult as you'd have to determine table names from the mysqllate field data types. I could abandon the report_field table idea and just create report_mysqllate_A, report_mysqllate_B tables etc that model exactly the number and type of fields for the particular mysqllates. But I'd like to maintain a master report table if possible. Also, down the road, it would nice to allow the user the option to design their own mysqllate. In which case I can't know ahead of time what fields I will need to assign to a report. I'm certain this must be a very common scenario, and I'm hoping that there is a much more elegant way of solving this I haven't seen yet. I tried searching online, but I found nothing. It's probably a terminology problem. I'm a db beginner so I don't know the phrases I should be looking up.
View Replies !
Create Column(table) Type With Exact Number Of Characters
i need to have column (for passwords) which will have exact number of characters, for example 10 characters, and i didn't know how to do it using sql (Mysql). I know that it is possible with Php, but i want to do this in the sql. Is it possible? ( and i don't want to use char(number) because i don't want to accept shorter passwords (or other data).
View Replies !
Create Table / Column Table Order
When creating a table, is there any advantage in specifying the columns in any special order? For example, what about columns with data used more frequently listed first, or column/data types giving some? Or.... am I being pedantic even thinking about such?
View Replies !
I Need To Convert Date Type To String Type
I need to retrieve a Date type data from the database and present the data in my GUI as a String type. I've tried like this : ResultSet rs; Date data; data = (String)rs.getDate("ColumnName"); gui_label.setText(data); but they say inconvertible types... Can someone teach me the way so that I can retrieve Date type data from the database and present it in the GUI as a String type.
View Replies !
2 Table, 5 Different Type Per Table
What I have: 2 table: First one is USERS, I have 3 type of USERS, A,B,C Second one is CALLS that have a caller_id, a called_id, a status (0,1,2,3,4), and a date. The field caller_id and the called_id are taken from table USER (field user_id) I need to show, aggregate by week how many user type A have call USER A,B,C divided by status of call, how many user type B have call USERS A, B, C divided by status of call, how many user type C have call USERS A, B, C divided by status of call. Without doing a large number of different query and without using excel to show the results...Is there a way?
View Replies !
ALTER TABLE Table ORDER BY ...
I have managed to create a database of 4.2 million rows. There are a few varchar fields and a couple of them are indexed with fulltext. Plus there are four numeric fields. I discovered when I only had about 400,000 rows that I could skim the top most 200 likely matches off the database with fulltext by first sorting the table with ALTER TABLE and then using a LIMIT 200. Since my data was not being added top the data was remaining sorted and this worked very quickly. But I just added another 3.8 million records and issued the command: * ALTER TABLE indexed ORDER BY (urank+blinks-quality-spareint) DESC; We are now about 11 hours later and SHOW PROCESSLIST is reporting a state of: * Repair with keycache Any idea how many more hours I might expect to wait for it to finish? If it might be a very long time is there a simpler way to do this? I have MySQL 5.0.27 and tried the following: * SELECT hash,url,title,decription,text,urank FROM indexed WHERE hash IN (SELECT hash FROM indexed where MATCH(title,text) AGAINST('keyword1 keyword2') LIMIT 200) ORDER BY urank DESC; But it tells me that LIMIT is not allowed with the IN function.
View Replies !
Does PK Field Type Have To Be Same As FK Filed Type?
I have a table with a primary key and, in another table, a foreign key to that 1st table needs to be set. should both fields have the same type i.e. where the PK is a 'int(11)' should the FK field be 'int' as well. And if so does it neeed to be limited to 11 as well?
View Replies !
What Type Do U Use To Store Currency Type?
What type do you use for storing price number? Decimal? If you use decimal, how many decimal point do u use? I use Mysql migration toolkit to convert my access db to mysql, and my Price column has been changed to decimal(19, 4). Anyone use decimal(19, 2)?
View Replies !
Order By Field From Other Table
("SELECT * from `offers` WHERE `status` = 'On' ORDER BY `pay_value` ASC"); I need to pull the "pay_value" from 2 different tables and then order it by that only. The first table is called `offers` and the field I'm referencing is `pay_value` type decimal(10,2) and the second table is called `exceptions` with a `pay_value` type decimal(10,2). What I'm doing is showing a list of ads with the amount of money that each ad pays per lead/sale. Some of our clients have been given increased payouts. Well, when they go to sort all of the ads in the system via the 'Pay Value' I can't make it show the increased payouts from the `exceptions` table while at the same time, being in correct order with the other `pay_value`'s from the `offers` table. I've tried the following: PHP Code:.....
View Replies !
Reorder Table Order
I've set up my MYSQL database and now I'm pulling data from the table along with field name of each field. I set things up in teh wrong order though so I need to reorder the table fields so that all I have to do is pull the data straight out instead of reorganize teh array after I've pulled it.
View Replies !
Order Table On Primary Key
I have searched for a while but cannot uncover the answer. I have a database, and for each table I want to know which field is the primary key (if any) and then sort the table on that field. I'm sure there must be a simple way to do it.
View Replies !
|
TRACKER
