Repair By Sorting
I have an application which uses MySQL to manipulate large amounts of data for short periods of time. The data set contains 13 columns each of which is indexed and typically about 30 Million rows.
I create a new table, load the data in to the table using load local infile from a CSV and then add indexes to each of the columns; the columns are a combination of integer, date and text. The text columns have a simple index and a full text index.
I need to be able to sort on each column (in a grid - I use DevExpress) and search on the textual fields.
I appreciate that getting the data into MySQL and creating the indexes is never going to be quick but I need to optimise this process as much as possible. Typically a data set would be loaded and viewed for a few hours and then a new dataset loaded and the process repeated.
When the index process is underway I get the above state displayed by show processlist and wondered if this is a problem. The whole load/index process takes about an hour and I would like to improve on this if I can.
I am also looking for suggestions as to how I may be able to best optimise this.
I use a dedicated server and the MyISAM engine.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Indexing Table Forces Repair By Sorting
I have a problem whereby i am loading a large table into MySQL - approx 3 million rows I then create indexes on most of the columns including normal and full text indexes on certain columns that I need to sort and search. The indexing operation takes an age (about 7-8 mins) on each column, whether it is a normal or fulltext index. Example: mysql> show full processlist; | Id | User | Host | db | Command | Time | State | Info | | 1196 | root | PAULS:2078 | registries | Query | 457 | Repair by sorting | CREATE INDEX VALNAMEidx ON FULLTAB_113 (VALNAME (64)) | 5 rows in set (0.01 sec) I have tried SET MYISAM_SORT_BUFFER_SIZE=1024000000 - this has no affect I have also tried creating the indexes when I create the table but I get the same thing. The tables are populated by load data local infile and no additional rows are added once populated I am running MySQL 5.0.11 on a dual core pentium 2.4Ghz with 2GB RAM Any ideas how I can speed this up?
How To Repair A Db ?
when I execute from command line (linux centos server) # mysqlcheck -o chatspa_main chatspa_main.BAmigMSG error : Can't find file: 'BAmigMSG.MYI' (errno: 2) chatspa_main.BuscaAmigos error : Can't find file: 'BuscaAmigos.MYI' (errno: 2) chatspa_main.CLKEXclkcontrol OK chatspa_main.CLKEXmain OK chatspa_main.CLKEXstats OK chatspa_main.CTICelebPhotos error : Can't find file: 'CTICelebPhotos.MYI' (errno: 2) chatspa_main.CTICelebrities error : Can't find file: 'CTICelebrities.MYI' (errno: 2) chatspa_main.ChatSpain_Users OK chatspa_main.MCmain error : Can't find file: 'MCmain.MYI' (errno: 2) chatspa_main.MCvotecontrol error : Can't find file: 'MCvotecontrol.MYI' (errno: 2) chatspa_main.phpCardData error : Can't find file: 'phpCardData.MYI' (errno: 2) chatspa_main.phpCardUsers error : Can't find file: 'phpCardUsers.MYI' (errno: 2) chatspa_main.phpCatData error : Can't find file: 'phpCatData.MYI' (errno: 2) How to fix/remove these "Can't find file" errors please ?
Repair Table
Is there a way to issue a "REPAIR TABLE table_name" command to all tables at once? Something like "REPAIR TABLE *"
Cannot Repair Tables
I lost my developers and an office i do hardware support for is down. I can do the basics, BUT checking and repairing the tables dont seem to work. I need some better direction. Here is a link to a text file that shows ALL of the errors. Can someone please reply with answers or even a direction to go. The is NO backup of the database and the data is vital. http://members.planetwebdev.com/web/admin/mysql/report.txt if "holy $#%@%#$" is the only answer you have. Please spare me your comments.
Repair Message
Running MySQL 4.0. I had an error in my database that wouldn't let me run selects (it said error 127 from table handler). I ran repair on the table and it fixed it dropping one row. I was just wondering what the meaning of the repair message is so that I can try to avoid the error in the future. Found link that points at 11548 (outside data file) at 10152.
REPAIR TABLE
Can REPAIR TABLE be used for rebuilding indexes on an InnoDB table? The only documentation I have seen is for MyIsam. There are 30 million records which has taken me 9 days to populate. I've made a backup but want to make sure this will work before I try it.
Keycache Repair
I ran the following querry on a large database: ALTER TABLE vbpost ADD FULLTEXT INDEX (title,pagetext). The querry is taking a very long time to execute (the database is also very large). I logged in with another shell and ran showprocess. It showed the querry in state keycache repair. Do I need to do anything at this point? Is there a way to tell if the querry is still running?
REPAIR Repeatedly
I'm running MySQL 4.018 on Fedora. I've got a few tables with more than 1M records, and if a larger number of deletes is thrown at them or a MYSQLDUMP is attempted, I get a "got error 127 ...". I've run "REPAIR", MYSQLCHECK and every other repair possibility I could find. The tables are supposedly ok after the repair operation, and can be queried normally. As soon as I try to run a MYSQLDUMP or a large number of deletes, I get the corrupted tables again.?
Can't Repair A Table
It seems I have a corrupt table and have tried the REPAIR TABLE command but during the operation I get this return. mysql> REPAIR TABLE users; +-----------+--------+----------+-------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+--------+----------+-------------------------------------------+ | sdc.users | repair | error | Can't open file: 'users.MYD'. (errno: -1) | +-----------+--------+----------+-------------------------------------------+ 1 row in set (0.00 sec)
Mysql Crash/repair
I've got a couple of directory sites that looks like somehow something's been deleted or crashed (the directory isn't showing any listings). I had a look in the mysql directory, and all I see are a bunch of .frm files. I don't see any MYI or MYD files. Is there any way to recover or repair? I'm assuming I need those files, and that the lack of them is what's causing my problems. We've done a couple of things lately that may have caused the problem like a server move and a hard reset.
Optimize Or Repair Db Tables
I bought a dating script online and been running it for 1 year, has about 1500 profiles, but the site is too slow now. Not about the host because I have godaddy host. When I ask the script owner, they told me that I need to optimize the db to make tables indexed. I already tried to go to phpmyadmin, click on all tables, then, select OPTIMIZE table from drop-down list,.... but it does not help. The site is still running too slow to load. I am using high speed intenet.
Repair Corrupt Tables
I have a couple of tables in a database the give me the following error when I try to open them: thanks! 'can't open file: 'clinch_a.ibd (errno:1)' What can I do to fix this problem.
Repair Corrupt Table
I am using xoops (a CMS) and MySQL on a website with not so much traffic. I am using "phpMyAdmin - 2.8.0.3-Debian-1" with "MySQL - 5.0.21-Debian_3-log" on a Debian unstable. Three tables have gotten corrupt recently and won't let themselves be repaired. When I use repair it says status OK, but if I try to delete a record or if I analyze the table again, it says that the table was reported as crashed and that it needed a repair. The only useful message I get, if I analyze the table is about wrong key at page 111616.
Server To Repair Mysql DB
I've got a corrupt 8GB mysql table and am obliged to use myisamchk -r -o -f --safe-recover But this is taking immensely long time, and is going slower and slower The time it would take to repair 2.7 million rows would be far too much than I can make my client wait.is there somewhere a service where I could sync a copy of the db files under /var/lib/mysql/, repair it on their powerful server, and bring it back home?
Repair Mysql Tables
is there some code to repair all the tables in my database in one go? instead of typing in "Repair Table <TableName>;" many times.
Auto Repair Tables
This is my first post on this board. Well.. now to my question. I´m running MySQL 4.1.11 on a linux-mandriva server. Some days, there are a few tables in on of my databases that gets "corrupted" for some strange reason. And this always happends, when i´m NOT around to repair the tables. So.. i was wondering if there is some kind of php-script or program, that could repair the tables automaticly
ANALYZE, OPTIMIZE, CHECK, REPAIR
when to use the ANALYZE, OPTIMIZE, CHECK, REPAIR commands. From what I can tell, ANALYZE and OPTIMIZE are for making the table optimal to increase performance. CHECK and REPAIR are used to find & fix corruptions. ANAYLZE takes a lot less time to run than CHECK. The questions I have are: 1. What is the general rule of thumb on regular (daily) maintenance of a MySQL DB? 2. Will ANALYZE report table corruptions? If so, I don't even need to call CHECK, I can directly go do REPAIR? 3. If the anwer to #2 is no, meaning I have to run CHECK TABLE, should I separate the optimization exercises from the find-corruption-and-repair routines?
Mysqlcheck: Table Is Marked As Crashed And Last Repair Failed
I'm using the command "mysqlcheck --all-databases --auto-repair --silent" and I see these warnings and errors (listed below) on a daily occurrence. What could cause these indexes to be out of order? Another thing I don't understand is why an increase in the number rows constitutes a warning? Hoping to here from MySQL users who have came across this. I'm using the MyISAM storage engine and MySQL 4.1.11 on Red Hat Enterprise Linux.
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
Check Table And Repair Versus Optimize Table
I am confused about the difference between running a CHECK table and REPAIR(if necessary) versus running an OPTIMIZE table. It's not very clear in the documentation, but seems that OPTIMIZE table does a check and repair in addition to the other functions performed by optimize. I have inherited some legacy code that does a check and repair and optionally follows it with an optimize. That seems redundant to me and I would like to streamline the code to make it an "either-or" selection.
Sorting
I have a database with 5 teams and each team has 'n' categories and each category has 5 sub-categories. i.e TeamOne = Ottawa, Category 1 = Player Name, Sub-Categories = Goals, assists, ppg, gwg, points. Team0ne Category 1 Sub 1 Sub 2 Sub 3 Sub 4 Sub 5 Category 2 Sub 1 Sub 2 Sub 3 Sub 4 Sub 5 Etc. From the examples above I am trying to add the all the sub-categories together for each category for each team in descending order. To put it simpler - I want a total of all the goals scored for each team and display them in descending order. I have been able to put a query together that gets me the totals, but I can not get them in decending order. The query below is in a for loop in PHP and cycles through each team and does give me the totals, just not in descending order (actually the order is the way they are numbered in the database); $query = "select team.name, sum(goals) from roster join reference join team where team.idn=reference.idn and reference.idp=roster.idp and position = 'F' and team.idn = 1 group by team.name";
Sorting
I have two columns, one is an int and the other is a date, I want the records to show first the null values in the int column and have that sorted by date, then the rest of the rows sorted by date. I tried doing order by int asc, date asc, but it only orders the null values properly. so this should be the result: date, int 3/12 null 3/13 null 3/8 5 3/9 4 3/10 4 3/11 5
Sorting
I need to sort a query's results from a database and I only want a particular value to be first. So if any of the query results equal "text" (the string) I want them to be the first values returned.
Sorting Twice
I want to sort my query by date then by name. Right now I'm using: SELECT * FROM `race_calendar` order by date asc How can I add the second sort?
Sorting
I am currently developing a used car website. When a user browses our inventory it displays the cars to him by the id it was entered, IE when entered into the database each car is assigned a number 1 , 2 , 3 so on. When the browse button is clicked it only sorts the vehicles by Car ID#. Is there anyway I can have it sort by price , or year , or make? Is the sorting in the database or in the scripting?
Sorting By Value
How can I fix this? Using the following to select and sort: $query_land = "SELECT id, type, estate, parcel, asking, zoning, `size`, selling FROM sold2005 WHERE type = 'land' ORDER BY estate ASC, selling ASC";
Sorting
Is there anyway to sort in a different order in mysql than it usually does. Here is an example. m100 m20 Mysql will order them in that order but the problem is that m20 should come before m100.
Not Sorting
some how my query is not sorting ASC or DESC, what am doing wrong? Code: SELECT DATE_FORMAT(jdate, '%a. %M-%d-%Y') as jdate, jid, jtitle FROM hrjobs WHERE jstatus ='on' ORDER BY jdate ASC
Sorting
Is there any way to sort a field in mySQL db when items are inputted into the DB, so they are sorted in the actual DB. I know they can be sorted easily in PHP on the client page, but I am not sure if there is any way for the DB to actually sort them. (It would be a numberic field, int).
Sorting
i have a mysql db of about 300-400 users which is used for a game, part of an update i'm working on is rankings, i want to know is there an easy way to sort the results of a SELECT query, so highest score first, lowest score last and then find what position 'user' is within the table?
Advanced Sorting
SELECT a.asset_id, CONCAT( a.subcategory_id, '-', a.asset_type_id) AS sort_key, SUM( av.vote ) as rank FROM asset a LEFT OUTER JOIN asset_vote av ON av.asset_id = a.asset_id WHERE a.status = 1 AND a.school_id = " . $SchoolID . " GROUP BY a.asset_id ORDER BY sort_key, rank LIMIT 100 I am working on a query and we would like to pick 100 assets. I would like one of each asset_type for each subcategory sorted by the highest rank. If there are only 3 subcategories and 3 asset types, that means I have 9 assets. If we have picked the "top" asset already it should go through the query again and pick the second one. I need to put 100 assets through, so if we run out, I would like to move to pull more from a different category. Any help from here? I am thinking of trying a group by on my sort_key?
Query On Sorting
I have a query SELECT atype, id, login, branch_name, contact_person, address1, address2, city, state, country, zip, telephone, fax, url, logo, branch_description, county FROM vendor WHERE id IN (?',��',?',?',��',?',��',��',��',��') If the ORDER BY clause is not present mysql sorts by the PRIMARY ID (id in this case) I want it to be sorted by the ids present in IN (?',��',?',?',��',?',��',��',��',��') any solution.
SORTING Problem
I am trying to create a query that will order a list of articles by 2 fields. First, it should order them by the date they were published, and then, it should order them by priority, with the newest articles and highest priority first. (There are multiple different priority 1 articles for example. Each publishing date has its own set of priority numbers from 1 to somewhere in the mid 40's). I tried a query like: SQL SELECT title FROM articles WHERE cat='breakingNews' ORDER BY priority,datePublished DESC But it didn't give me the results sorted as they should be. After a Google search, I also tried this: SQL SELECT title FROM articles WHERE cat='breakingNews' ORDER BY (priority/datePublished) DESC Yet neither one provides me with the data sorted as I need it. Can anyone help me with this query?
Sorting By Two Columns?
Is it possible to ORDER BY one column ASC and then by another within the first order? You know like when you do a data sort in excel, you enter your first column and then your "then by" column and that second sort only takes place within the first? PS: I tried using the search function for this, but was unable to find anything.
Sorting On Date
I want to show a list that is ordered by birhdays, where the person who have birthday soonest will be at the top of the list. Can anyone please give me the syntax for this request.
Sorting Within GROUP BY?
I've been working hard at this for a while, and I've figured a few things out, but I'm having trouble sorting the below query by "mls_condos.date_posted": CODE"SELECT SQL_CALC_FOUND_ROWS owyw.*,mls_condos.name,MAX(mls_condos.date_posted),COUNT(mls_condos.date_posted) FROM owyw LEFT JOIN mls_condos ON mls_condos.name=condo_names.mlsname JOIN condo_names ON owyw.development=condo_names.mcbname GROUP BY owyw.development ORDER BY mls_condos.date_posted DESC"
Sorting With Umlauts
I've spent most of today trying to figure out how to get my query to sort correctly. I've read the CHARACTER SET and COLLATE documentation without any luck. I want my data to be sorted like this (it is the correct way to sort in Swedish): a b c z å ä ö With my current settings, however, I get: a å ä ö b c z I have changed the database and tables to the following configuration: mysql> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql> show variables like '%coll%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ I have also tried to query with collate, like this: SELECT title FROM papers ORDER BY title COLLATE latin1_swedish_ci desc Still, the data is not sorted as I want it.
Sorting My Results
This is the sql Im using in a recordset using dreamweaver: SELECT racer_name,SUM(points) FROM race_results WHERE `class` = 'gas' GROUP BY racer_name ORDER BY race_results.points DESC Im really new to this but the "SUM" is working fine, but im having a problem with ORDER By function,...its order by "points" which in my DB is a samllint ---- It just needs to sort by that number (points) by largest to smallest,
Sorting Problem
I have a small problem. I have this sql schema: `ticker` varchar(10) NOT NULL, `host` varchar(50) NOT NULL, `charname` varchar(30) NOT NULL, `amount` int(10) NOT NULL, `timestamp` int(15) NOT NULL, `valid` int(15) NOT NULL, Now, each "host" has "zero to n" rows, each row lists... -> ticker (small identifier) -> host (ticker from above as subdomain.domain.tld) -> charname (character who paid) -> amount (how much he/she paid) -> timestamp (when he/she paid, UNIX) -> valid (a period, relative to zero, in seconds) Now, after more than one payment, a host has several rows. I want to "select" all DISTINCT tickers and their rows where timestamp is max'ed. So if i have 5 tickers i would like 4 results, each result the most recent (talking in timestamp) entry into the database.
Sorting On Two Columns At Once
I have a table with 2 columns called "lastname" and "company" that I would like to sort. Sometimes the lastname column is null, and in that case I would like to use the company column for the sort. Is this possible? E.g. this data: [lastname, company] smith, amway jones, IBM <null>, microsoft should output the rows in this order: jones microsoft smith
Sorting From Mysql ....
I am pulling data out and sorting on the postcode - but have been asked to put it in 'proper' order - ie non-acii-betically .. I need - BB1 BB2 BB10 BB25 NOT - BB1 BB10 BB2 BB25 Can we do this in the 'select' statement ?
Sorting Enum's
Is there a way to easily sort Enum's in the SQL query by the data rather than their position in the enumeration array? i.e. I have a field called priority, it holds the values 1-10, A-D, Must, High, Medium, Low, Very Low, '' (The last being blank string) It sorts by the order I put them into the enum, which is actually better in this particular case... but I'm curious, if I wanted to sort by the normal alphabetical method on this column, could I?
Grouping / Sorting
However the data is brought back and sorted by month and I was wondering if it's possible to bring back the data the has franid,barid in every month of the year. So right now, as you can see showing the results for the first 3 months, Month 2 has data in the second row I don't want returned and in any calcultions. The franid, barid combo has to be represented in each and every month, in this particular case between Month 1 and Month 12. 1234, try, 7894.89, 1 1456, tre, 7842.78, 1 3526, gft, 5690.00, 1 1234, try, 6794.89, 2 6789, ght, 7842.78, 2 3526, gft, 5340.00, 2 1234, try, 2394.89, 3 1456, tre, 4542.78, 3 3526, gft, 8990.00, 3
Sorting Top Three From Any Number
Sorting top three from any number I am doing a quiz. A player can have as many goes as they like e.g. Fred has 6 goes, Jessica has 5 goes, Ann has 4 goes and they score: Fred 3 6 3 7 2 4 Jessica 5 4 6 7 3 Ann 3 4 6 5 How do I sort the data alpahbetically and by number so that ONLY the top three scores for each player are shown: Fred 7 Jessica 7 Ann 6 Fred 6 Jessica 6 Ann 5 Jessica 5 Ann 4 Fred 4
Sorting Addresses
I am working with MySQL to output a list of addresses. What I'm trying to do is sort the output by the address, but instead of sorting by the leading numeric values, I want to first use the alpha values and then the leading numeric values. For example, say I have the following records in a table called USERS in the column Address1: 100 Fifth Avenue 48 Fifth Avenue 19 Seventh Street 123 Ninth Street 20 Eleventh Street If I use the command "SELECT Address1 FROM USERS ORDER BY ORDER BY Address1 + 0", the result will be this (since MySQL would sort by the leading numbers): 19 Seventh Street 20 Eleventh Street 48 Fifth Avenue 100 Fifth Avenue 123 Ninth Street What I am instead trying to accomplish would be output that looks like this: 20 Eleventh Street 48 Fifth Avenue 100 Fifth Avenue 123 Ninth Street 19 Seventh Street Essentially, I need an ORDER BY clause that allows me to first sort by the street name and then will sort each individual house number per street name in increasing order. What can I do to accomplish this without creating a separate column for each street address and street name?
Sorting Problem
I have an issue with a program I'm writing. I need to sort a list of projects by the project number. The problem I'm facing is that the "numbers" are formatted as follows: ###/##-###.#.# and of course if I have one say 500/00-125 and another 500/00-50, the 50 is going to come before the 125. This is a problem for the people using the program. I was wondering if there was any way to sort these numbers better. I'm just using 'ORDER BY projectnum', as it's the only way I know how to do it. I'm using MySQL and connecting it to a Delphi program via BDE if any of that matters to you.
Sorting Problem
Lets say you have data like this: Time Sessionid 20.55 a1 20.44 a1 20.39 x3 20.33 x3 20.29 a1 20.27 a1 and you want to sort it so it looks like this i.e. grouped according to Sessionid and then Time: a1 = 20.55 20.44 20.29 20.27 x3 = 20.39 20.33 What is the best way to do this in mySQL?
Sorting (ORDER BY)
in MySQL are rows with these values: 995 25120 991 99 1132 86 I would like to sort them. But when I tried to use this SELECT * FROM table WHERE something='$something2' ORDER BY numbers DESC result was this: 995 991 99 86 25120 1132 and that's absolutely wrong! Result I want is something like that: 25120 1132 995 991 99 86
Multiple Sorting ?
In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean. I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well.
|
TRACKER
