Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records.
This is very new to me and I am noticing that my queries are really slowwwwww!
What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.
View Complete Forum Thread with Replies
Related Forum Messages:
Optimizing Join W/ Large Table
Here is a high level description of my tables I have a table called Items items are grouped by an ID, but it is NOT unique I have an index on ID for the Items table and a table called CompletedItems completed items consolidates information about the items, and here the ID is unique. I have unique index on ID for the CompletedItems table What i need is a query that will return all item id's which have not been completed. I have this query: select distinct(items.id) from items left join (completeditems) on (items.id = completeditems.id) WHERE completeditems.state is null; this gives me the data I want except there are two problems a) this query takes a long time to run. while it is running, the completed items table is locked, which causes several other processes to block while this is happening. to give you an idea of scale, the items table has about 20 million records in it, and the completed items has about 4 million.
View Replies !
Optimizing A Large Phone Directory
I have a table with names and contact information for a bunch of people (we'll call it a phone directory -- it's not, but confidentiality prevails ). To get twenty people to display on a single page, I'm using this query: SELECT * FROM people ORDER BY lastName LIMIT 20 Pretty straightforward, but for some reason, with a few hundred thousand rows, this is taking a very long time (and I do have an index on lastName, which doesn't seem to help). Is there anything I can do to speed this up? Also, would it be quicker to have separate firstName and lastName lookup tables with firstNameId and lastNameId columns, using this query: SELECT p.*, f.name as firstname, l.name as lastName FROM people p LEFT JOIN firstNames f ON f.id = p.firstNameId LEFT JOIN lastNames l ON l.id = p.lastNameId ORDER BY l.name LIMIT 20 Unless there's some obvious problem or solution I'm overlooking, the only other thing I can imagine is having ANOTHER table with the alphabetical order of the last names, which might speed things up but seems somewhat unwieldly (though doable, if necessary, since I won't be adding people too often). Hopefully there's some simple problem I'm not seeing (which may be, since it seems like MySQL should scale better than this).
View Replies !
Optimizing Tables
I have big problem with mysql 4.0.18 . Because my database is growing up, every night records that are older than some date are deleted. But size of database on disk isn`t smaller after this operation. There are some solutions to free space by OPTIMIZE TABLE command, but this command when executes, does temporary copy of database. How can I free memory and avoid doing temporary copy of database ( because I don`t enough free space for this
View Replies !
Optimizing Tables?
I was wondering if was possible by running some SQL query to tell if a table needs to be optimized or not. And if a table didn't need to be optimized, and I was to run the optimize table command, would it preduce and ill effects?
View Replies !
Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much out of the box setup, and am starting to suffer heavily with my larger tables. I have a table with 5,000,000+ rows that I have to search and do joins on. Although I have an index set up for it, the joined select will still take some 400+ seconds to return, which is obviously unacceptable. This is due to enormous HD access. Perhaps someone can help me with indexes here; I was under the impression that the index for the tables are cached in memory, and therefore permitted "instant" searchability, without having to retrieve data from each of the rows of the DB. Is there a startup parameter, or something in the mysql.ini file, that must be set to allow for this? I have the index configured properly, and have made sure that the query uses there parameters in the where clause in the same order that they appear in the index. Code:
View Replies !
Large Tables
So i've got a table with 15 million rows. id is the primary key SELECT COUNT(id) FROM listings WHERE id != 0; Takes about a minute to load... which is insanely slow.. anyone know what could be causing this?
View Replies !
Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example: select myfunc(x,y,z,a,b,c) from events where energy>3; But as far as I know there is no way of defining your own functions in SQL
View Replies !
Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life: If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient? The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key. The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?
View Replies !
Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user. Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details. Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?
View Replies !
Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables. My question for performance, as this table grows should it eventually make a new table? If so here is my current select code select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1' Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?
View Replies !
Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....
View Replies !
My.cnf Optimizing MySQL
Just wondering if anybody has a suggestion for tweaking the my.cnf file to reflect my server set-up. I'm running Dual pentium III 550, 1G Ram, mirrored 8G SCSI HD. Connections to the server are all through PHP about 90% SELECTS and 10% INSERTS/UPDATES. It is a college sports site so I get some pretty heavy traffic on game days the rest of the time it's moderate to semi-heavy. Currently I'm using the default settings but get major slow downs on game days.
View Replies !
Optimizing Mysql
i have Mysql 5.0 and i have huge amount of data in my database and it i think making my website very slow. i just want toknow how can i improve my database performance and what things i have to do and how?
View Replies !
Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl= e categories of entries, the database is quite large about 200 meg.=20 I would like to know if using join tables is more efficient than storing th= e keys in a varchar field then within the second loop doing a where in (1,2= ,3,4) where the 1,2,3,4 are the stored category keys in the varchar field = rather than a where in (1), where 1 is the pirmaryID of the entry for insta= nce ?
View Replies !
Slow Join On Large Tables
I have two tables: D (500,000 recs), and DL (2,500,000 recs) D has a PK and an index on HLQ. DL has a PK and an index on ID. The following SQL: SELECT HLQ as "HLQ", count(*) FROM D, DL WHERE D.DLID=DL.ID GROUP BY HLQ produces the following explain: tabletypepossible_keyskeykey_lenrefrowsExtra DALL500000Using where; Using temporary; Using filesort DLeq_refIDID4D.DLID1Using index The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00). key_buffer=512M table_cache=256 sort_buffer=16M read_buffer_size=16M It appeasrs that the 'Using filesort' on table D is due to the Group By clause and is the problem. I have an index on HLQ. Is there any way to get MySQL to use it?
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else. I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger. This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen. Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go. I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.
View Replies !
Large Unused Tables Vs Performance
I've been working on a website with a rather large a database. One of the about 100 tables in there was about 1/4 of the total database size. The table had no use anymore at the time I worked the website, so it was just sitting there, doing absolutely nothing. Obviously to spare diskspace we deleted the table. But the question I have now, can unused large tables affect the perfomance of Mysql? I've searched a bit around, but honestly I couldn't find a clear answer about it right away (maybe I haven't searched enough either :) )
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
Large Number Of Tables And Columns
i'm developing an online portal, which my MYSQL database contains 100 tables.. my doubt is if any table contains 30-40 columns will it become a problem? like my html front end form has 40 fields for data insertion. and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems? as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?
View Replies !
Optimizing MySQL For Performance
I am going to be running mySQL on a RAID5 array and am wondering if I need to worry about splitting up the data and log files on to seperate channels on the RAID array. I have heard differing arguments. Some people say that you can install SQL on the same logial drive as long as you are running RAID5 and not worry about performance issues. Others say that you need to split everything up even if you are running RAID5. Do I need to split it up in to 2 channels and 2 logical drives or is one drive OK?
View Replies !
Optimizing MySQL Fulltext
I got a DB with about 7.000.000 rows, that I want to scan by mySQL fulltext search. Now this keeps taking loads of time whenever I try to. Is there a way to speeden that up a little? I did so far these things that I could think of would probably speeden it up a little: * remove the stopword list * do a brand new fulltext index The whole DB runs on my own server, which is for that DB only and is a Dual Xeon 2.8 with 1 GB RAM, so I guess that should last cpu wise, but then I am not sure on that one as well. Would I need a better CPU?
View Replies !
Optimizing MySQL Databases
I bet you're about to say "Well, use the OPTIMIZE function." See, the problem is that the OPTIMIZE function isn't what I'm looking for. In all honesty, I don't even know what the OPTIMIZE function can do, but I know what it can't. Here's an example of the problem ID | Bytes | Field | ... ---+-------+-------+ 1 | 0001 | ????? | (Ignore the data) 2 | 0010 | ????? | 3 | 0011 | ????? | 5 | 0101 | ????? | <-- Here's the problem 6 | 0110 | ????? | Well, I think you get the idea. The problem is the gap in between the auto_increment value ID from row 3 to 5. What I'm wanting it do is 'compress' the rows and get rid of that little increment gap, like this: ID | Bytes | Field | ... ---+-------+-------+ 1 | 0001 | ????? | 2 | 0010 | ????? | 3 | 0011 | ????? | 4 | 0101 | ????? | <-- Here's the fixed problem 5 | 0110 | ????? | So, would anyone know any MySQL or PHP to help this out?
View Replies !
Speed Of InnoDB DELETEs On Large Tables
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the following select takes a couple of seconds): [color=blue] > SELECT count(*)[/color] -> FROM UserSnap -> WHERE LogDate<now() - INTERVAL 750 DAY; +----------+ | count(*) | +----------+ | 308969 | +----------+ [color=blue] > DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;[/color] That delete query takes hours to run. The structure of the table is: [color=blue] > desc UserSnap;[/color] +----------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------------------+-------+ | LogDate | datetime | | PRI | 0000-00-00 00:00:00 | | | Period | tinyint(4) | | PRI | 0 | | | UserName | varchar(50) | | PRI | | | | RateType | varchar(50) | | PRI | default | | | Rate | float | YES | | NULL | | +----------+-------------+------+-----+---------------------+-------+ Any suggestions on why this is slow, and what to do about it?
View Replies !
Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL. I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools. The performance decreased gradually and crashed at ~60% completion. Now I'm planning to use xml2sql tools to convert the dumps to txt format. Then I'm planning to load this using mysqlimport. I would like to run this load faster, and on Oracle, I would use the IMP parameters such as INDEXES=n and so on. What are the recommended steps when using mysql? Should I target any of the system variables? I have a 4G ram on the server and can use all of it for the load. Any pointers?
View Replies !
Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions: 1-what can I do to avoid the system stop or time out message? 2- To use several tabels infomation should I use 'View' command or can I use other methods? 3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table? (I use postgresql).
View Replies !
Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down. I have 4 important tables here (I'm only posting the columns which I use): `auth` UID mediumint(8) unsigned PRI NULL auto_increment username varchar(32) MUL clan_id mediumint(8) unsigned MUL 0 clan_abbrev varchar(7) MUL `clans` ID mediumint(8) unsigned PRI NULL auto_increment Abbrev varchar(7) MUL Name varchar(64) icon_id smallint(5) unsigned 0 `stats` UID int(10) unsigned PRI 0 kills mediumint(9) NULL `usernames` UID int(10) unsigned 0 ID int(10) unsigned PRI NULL auto_increment Name varchar(32) Uses mediumint(9) 0 `auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`. I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`. The result should be ordered by `Uses` (from `usernames`) -- highest to lowest. The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type). Here's what I managed to hammer out with my limited knowledge of SQL: ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `clans` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND clans.ID = auth.clan_id AND stats.UID = auth.UID ) UNION ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND auth.clan_id =0 AND stats.UID = auth.UID ) ORDER BY `Uses` DESC It works great, but the average query takes around 6s (these tables have several thousand entries in each).
View Replies !
Optimizing MySQL For A New Server Setup
We are currently switching over to a newer more powerful server. I am wondering if anyone could give me their input on what would be the optimal configuration of MySQL for the following conditions.... Dual Intel Woodcrest Dual Core 4 GB RAM NCQ SCSI SAS RAID drive MySQL 5.0.45 PHP 5.2.3 There are 2 databases on the server which combined total approximately 4 million records across 120 tables with a disk footprint of about 1GB. Traffic fluctuates from between 3,000-6,500 page views per day. After we finish the upgrade we expect traffic to grow by 1.5 to 2 times that since the server will not be overwhelmed as much. Code:
View Replies !
Optimizing MS Access Front End -> MyODBC <- MySQL Back End
I am using an MS Access front end -> MyODBC <- MySQL back end database configuration where the data is replaced every month. After emptying the old data, I would like to optimize the MySQL tables using VBA code, prior to importing the new data. Would anyone know how to implement this in VBA code, using ADO and ODBC
View Replies !
Optimizing
I have a strange performace problem, I can't seem to understand. It's the follownig query: PHP Code: select tblrelations.ID from tblcontracts inner join tblrelations on (tblcontracts.relation_id=tblrelations.ID) where (tblrelations.ID='106512' or tblrelations.mainrelationid='106512') order by tblcontracts.ID desc
View Replies !
Optimizing My.cnf ?
I have got a server with 320MB ram. This is a free forum hosting server. Many people create own forum every day. So apache+mysql are very important for me. Could you help me with optimizing my.cnf for my MySQL 4.1.14 server ?
View Replies !
Optimizing Imports
I need to have a web application be able to import large amounts of data (400,000 rows of 10 columns). I know how to script it and have it running in the background. However I want to know how I can optimize my insert statements to try to speed things up. Will it help if I insert multiple rows at a time? If so, is there a magic number or range?
View Replies !
Optimizing A RAID
Opteron workstation 8-disk Fibre Channel RAID setup (should run at about 800 MB/s) I am strictly WRITING to a database consisting of ~10 tables which range in size from 100 MBs to 15+GB. Each INSERT statement contains 1 key and between 5 to 40 double values. Some of my questions include: 1) Do I set my block size small (to accomodate the single INSERT writes) or big (to accomodate the large DBs)? 2) Is there a system tool that can be used to determine the average block size that I am writing?
View Replies !
Optimizing Table
I have a db that's an overflow, meaning all it does is collect data (inserts only). I then have another server that checks for records that may exist there, and if it finds them, pulls them, processes them into other databases, then deletes the overflow record(s). While this works great, the collection table, on every delete, stats getting overhead(seeing this via phpmyadmin). Over the course of a few hours this can add up to be several megs in size. It does not seem to be effecting performance, however I think it may in time. My question is, would it hurt anything to run a OPTIMIZE TABLE 'collect' command after every delete?(seems crazy) (this system processes 10-15 records per second) Any other suggestions?
View Replies !
Optimizing FIND_IN_SET()
This is the case: I have a table with a row that contains a big csv. I use FIND_IN_SET to query that csv. This is causing performance issues. The type of the field is TEXT, but TEXT cannot be indexed. Yeah, to fulltext, but than the queries have to be adjusted to MATCH and don't have the working i ment.
View Replies !
Structure / Optimizing
I'm just after migrating my access database to mysql, the structure and data types are a bit confusing though, I have two tables each one has 22 fields, I'm not sure what data types to use though, I have attached a excel file with the data types, descriptions and examples of data for the two tables,
View Replies !
Optimizing Another Query
I have another Query, that I don't manage to optimize. It's hard. I tried to first readout the ID's and then readout the contents, but this is not faster ... Here is the Query: SELECT o.id AS oid, o.time AS otime, o.disNaviReferer AS showR, o.disNaviToplist AS showT, o.disNaviPartner AS showP, o.description AS odesc, o.type, o.used, o.html, c.id AS cid, c.name AS catname, e.id AS eid, e.beschreibung AS linkdesc, e.flag, e.freigeschaltet AS etime, e.previewPic as prev, e.hits FROM own_content o LEFT JOIN link_entries e ON e.link = o.id LEFT JOIN link_cats c ON c.id = e.cat_id WHERE o.used = 1 AND type = 4 ORDER BY etime DESC LIMIT 0, 30 Here ist the table structure: -- -- Tabellenstruktur für Tabelle `link_cats` -- DROP TABLE IF EXISTS `link_cats`; CREATE TABLE `link_cats` ( `id` tinyint(2) unsigned NOT NULL auto_increment, `name` varchar(100) collate latin1_general_ci NOT NULL default '', `cflag` tinyint(2) NOT NULL default Ƈ', PRIMARY KEY (`id`), KEY `flag` (`cflag`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `link_entries` -- DROP TABLE IF EXISTS `link_entries`; CREATE TABLE `link_entries` ( `id` smallint(11) unsigned NOT NULL auto_increment, `flag` tinyint(2) NOT NULL default '-1', `eingetragen` int(10) unsigned NOT NULL default Ɔ', `freigeschaltet` int(10) unsigned NOT NULL default Ɔ', `cat_id` tinyint(2) unsigned NOT NULL default Ɔ', `beschreibung` varchar(100) collate latin1_general_ci NOT NULL default '', `link` varchar(255) collate latin1_general_ci NOT NULL default '', `extern` tinyint(2) NOT NULL default Ƈ', `previewPic` varchar(255) collate latin1_general_ci NOT NULL default '', `hits` int(11) NOT NULL default Ɔ', `voteAnzahl` smallint(10) unsigned NOT NULL default Ɔ', `VoteSumme` mediumint(10) unsigned NOT NULL default Ɔ', `poster` varchar(100) collate latin1_general_ci NOT NULL default '', `poster_hp` varchar(100) collate latin1_general_ci NOT NULL default '', `ip` varchar(15) collate latin1_general_ci NOT NULL default '', `mail` varchar(100) collate latin1_general_ci NOT NULL default '', PRIMARY KEY (`id`), KEY `cat_id` (`cat_id`), KEY `flag` (`flag`), KEY `link` (`link`), KEY `freigeschaltet` (`freigeschaltet`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6242 ; -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `own_content` -- DROP TABLE IF EXISTS `own_content`; CREATE TABLE `own_content` ( `id` smallint(5) unsigned NOT NULL auto_increment, `html` text collate latin1_general_ci NOT NULL, `description` varchar(255) collate latin1_general_ci NOT NULL default '', `time` int(10) unsigned NOT NULL default Ɔ', `ip` varchar(15) collate latin1_general_ci NOT NULL default '', `used` tinyint(3) NOT NULL default Ɔ', `last_used` int(10) unsigned NOT NULL default Ɔ', `used_by_fl_id` smallint(5) unsigned NOT NULL default Ɔ', `type` tinyint(2) NOT NULL default Ƈ', `disNaviReferer` tinyint(2) NOT NULL default Ƈ', `disNaviToplist` tinyint(2) NOT NULL default Ƈ', `disNaviPartner` tinyint(2) NOT NULL default Ƈ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=299 ; Thank you very much! This query is very important for me to be optimized, because I execute it quite often with diferent data ...
View Replies !
Need Help Optimizing ORDER BY
I cannot seem to properly optimize this statement used to show topics across all forums that have been recently replied to: SELECT t.topic_id, t.topic_title, t.topic_sticky, t.topic_views, t.num_posts, f.forum_icon, f.forum_name, p.poster_name, p.post_time FROM minibb_topics AS t LEFT JOIN minibb_forums AS f ON t.forum_id = f.forum_id INNER JOIN minibb_posts AS p ON p.post_id = t.topic_last_post_id WHERE 5 >= forum_level_to_view && ( forum_site =0 || forum_site =1 ) ORDER BY t.topic_sticky DESC , t.topic_last_post_id DESC LIMIT 20 I have isolated the issue to the ORDER BY clause. If I remove the ORDER BY clause, the query takes 1/10th of the time to execute (0.150 seconds as opposed to 0.015). Both ORDER BY fields are indexed. Also, if I limit the query to one forum such as, "WHERE f.forum_id = 1", the statement executes in 1/10th of the time. This is the explain: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE f ALL PRIMARY,forum_level_to_view,forum_site NULL NULL NULL 12 Using where; Using temporary; Using filesort 1 SIMPLE t ref forum_id,topic_last_post_id forum_id 2 aanime_aanimedb.f.forum_id 651 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 aanime_aanimedb.t.topic_last_post_id 1 This is the table structure: -- -- Table structure for table 'minibb_forums' -- CREATE TABLE minibb_forums ( forum_id smallint(6) NOT NULL auto_increment, forum_name varchar(150) NOT NULL default '', forum_desc varchar(255) NOT NULL default '', forum_order tinyint(2) NOT NULL default Ɔ', forum_icon varchar(255) NOT NULL default 'default.gif', forum_group varchar(30) NOT NULL default '', forum_site tinyint(1) NOT NULL default Ɔ', forum_level_to_view tinyint(2) unsigned NOT NULL default Ɔ', forum_level_to_post tinyint(2) NOT NULL default Ɔ', forum_level_to_topic tinyint(2) NOT NULL default Ɔ', num_topics mediumint(8) unsigned NOT NULL default Ɔ', num_posts mediumint(8) unsigned NOT NULL default Ɔ', is_spam tinyint(1) NOT NULL default Ɔ', PRIMARY KEY (forum_id), KEY forum_order (forum_order), KEY forum_level_to_topic (forum_level_to_topic), KEY forum_level_to_view (forum_level_to_view), KEY forum_site (forum_site) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'minibb_posts' -- CREATE TABLE minibb_posts ( post_id int(11) NOT NULL auto_increment, topic_id int(10) NOT NULL default Ƈ', poster_name varchar(16) NOT NULL default 'Anonymous', post_text text NOT NULL, post_time datetime NOT NULL default ��-00-00 00:00:00', poster_ip varchar(15) NOT NULL default '', post_status tinyint(1) NOT NULL default Ɔ', PRIMARY KEY (post_id), KEY topic_id (topic_id), KEY poster_name (poster_name) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'minibb_topics' -- CREATE TABLE minibb_topics ( topic_id int(10) NOT NULL auto_increment, topic_title varchar(100) NOT NULL default '', topic_sticky tinyint(1) NOT NULL default Ɔ', topic_poster_name varchar(16) NOT NULL default 'Anonymous', topic_time datetime NOT NULL default ��-00-00 00:00:00', topic_views mediumint(9) NOT NULL default Ɔ', forum_id smallint(6) NOT NULL default Ƈ', topic_status tinyint(1) NOT NULL default Ɔ', topic_last_post_id int(10) NOT NULL default Ƈ', num_posts mediumint(8) unsigned NOT NULL default Ɔ', PRIMARY KEY (topic_id), KEY forum_id (forum_id), KEY topic_poster_name (topic_poster_name), KEY topic_sticky (topic_sticky), KEY topic_last_post_id (topic_last_post_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
View Replies !
Optimizing A Query
I want to optimize a very expensive query, the query joins 5 tables, each having tens of thousands of records, always returns about 15000 matches and performs several calculations on each and every records (a datadiff, five time the / operator and two * operators), and to end with, all records (probably about 45000) are grouped and sorted resulting in 15000 records. This takes about 80 seconds, I want to get it down to max. 2 seconds :-D All indeces are made, so I won't be able to gain anything there anymore. Does anybody has a clue as to how I could proceed here (maybe something with caching, materialized views and triggers?).
View Replies !
Optimizing ORDER BY
Let's say you have a table with 100,000 rows, and a field that can have an integer value from 0 to 100000000. SELECT * FROM table ORDER BY field DESC LIMIT 15
View Replies !
MySQL With Large Table.
I am using MySQL for a table which will have 100M+ records, avg length of records being 130 bytes. When the number of records reach approx. 25M (and the file size close to 4GB), the rate of inserts falls drastically from 800 per second to 30-40 per second. Details: * MySQL 3.23.58 on Fedora Core 3 * Table has 4 indices. * I have got rid of 4GB file size problem with MAX_ROWS=1000000000. * File system : ext3 on single disk. ext3 could create 10G file without much trouble. So I am convinced that ext3 is not the bottleneck. * Tried using InnoDB engine but it also doesn't meet the requirements. Requirements of database: * A single table in the database with 100M+ rows, each of size 130 bytes (approx). * 500-600 inserts per second. * 200 selects and 200 updates per second. (These statements will affect only one row) * 3-5 select statements per minute which can return 10k to 500k records. * No foreign keys/ACID transaction requirements. * Fast recovery in case of crash. Questions: * Does MySQL performance drops when the table grows beyond 4G? * Which are most important server variables which need to fine tuned? Currently I am using only key_buffer = 512M.
View Replies !
Large MySQL Database
There are many threads on here about large db designs etc but I'm looking for a little information I have been unable to find anywhere. What sort of design to places like facebook/myspace use for their database(s). Is it best to have a table for user info (id, username, email, etc) then have a table for every feature of your site (blogs, pictures, friends, comments etc etc) or do they have a table for every user. I would assume the first as a table for every user would soon grow very very large and become inefficeint.
View Replies !
Large Database In Mysql
I have a database with two tables in MySQL 4.1. Die one tabel contains about 300,000 records and the other table contains about 70,000 records. I tried joining and subquering (very simple sintax) these two tables and received no result. The processor of the server was being tied down. I went to a bigger computer with the same results. I then tried minimizing the tables and had to go to about 10000 and 8000 records respectively, before it worked. I did not try indexes, because I wanted to make a comparison between no indexes and with indexes. Granted I did not tweak many server variables, but shouldn't this just make the query slower and not completely tie it up?
View Replies !
Importing A Large Db To Mysql
I have a DB creation script of size about 50MB. I cannot seem to load it to the mysql db I have installed at home (I have a P3 , 512MB RAM), as my computer runs out of memory. Is there a way to force mysql (ver 5.0) to flush the memory from time to time to the HD? Is there a way to break the script to several small sub-scripts?
View Replies !
Optimizing Range Queries
I have a database table with about 400,000 rows, and each has an x and y co-ordinate as a float. I am doing a query such as: SELECT COUNT(*) FROM property WHERE x BETWEEN 57.003445 AND 58.134994 AND y BETWEEN 0.93572455 AND 1.42349875; This tends to take around 800ms even with an index on (x,y), which is far too long. Any thoughts?
View Replies !
|
TRACKER
