MYSQL :: Optimizing Tables

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.

Thanks to all.

#Joseph Norris (Perl - what else is here?/Linux/CGI/Mysql)
print @c=map chr $_+100,(6,17,15,16,-68,-3,10,11,16,4,1,14,-68,12,1,14,8,
-68,4,-3,-1,7,1,14,-68,-26,11,15,1,12,4,-68,-22,11,14,14,5,15,-90);

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 ?

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

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 &#55612;&#57200;-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 &#55612;&#57200;-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;

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?).

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

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 ...

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?

Optimizing Mysql
Can someone explain to me what is the "Key Effiency" counter in mysql administrator. It basically is flatlined at 100% I have 257M entered in Key Buffer under Startup Variables. The machine has 2Gs of Ram. What would be an acceptable(recommended) amount of buffer size I could use.

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?

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?

Optimizing And Reindexing
I just deleted a bunch of old records from a Mysql table. The table has 32 indexes. Initially the phpMyAdmin showed that the table was 190MB and now it is at 186MB. I expected the size to be much smaller but its not. Do I need to compact mysql? If so can anyone give me an idea? Also, do I need to reindex the table ?

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,

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.

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.

Optimizing The Queery
I was wondering are there technoques to optmize your queery? Like someone told me id you do search on certian fiels make thme index key. And in you SQL do not join too many tables etc .

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?

Optimizing MySQL
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

Optimizing A SELECT Query
SELECT id, gameid, name, type FROM media ORDER BY date DESC LIMIT 5
SELECT * FROM games ORDER by wishlist DESC LIMIT 5'
SELECT * FROM games ORDER by mygames DESC LIMIT 5
SELECT mediaid, count(*) AS number FROM download WHERE date > &#55614;&#57159;-08-14 10:44:43' GROUP BY mediaid ORDER by number DESC LIMIT 5
SELECT * FROM news ORDER BY id DESC LIMIT 14'
SELECT gameid, count(*) AS number FROM views GROUP BY gameid ORDER by number DESC LIMIT 10
All of these queries I have determined are inefficient (using some tools) and they all have the same thing in common. They're all trying to find the "Top 5" or "Newest 10" or whatever from a table, but in doing so they are going through every single value and comparing them.

What other way is there to do this same idea of getting the "top 5" of a certain column values, without the SQL having to check every entry?

Help Optimizing UPDATE Query
I have 1 query in my script that is taking waaaaay too much time.

Is there a way that I can speed up or optimize this query?

Right now the query is taking about 600+ seconds to update 500 rows.

The Query

PHP

$update_from_sas_postmeta = "UPDATE wp_postmeta, sas_postmetaSET wp_postmeta.meta_value = sas_postmeta.meta_valueWHERE wp_postmeta.post_id = sas_postmeta.post_idAND wp_postmeta.meta_key = sas_postmeta.meta_key";$update_from_sas_postmeta_result = mysql_query($update_from_sas_postmeta) or die("MySQL ERROR (update_from_sas_postmeta_result): ".mysql_error());

The Tables

CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=294705 ;

CREATE TABLE `sas_postmeta` (
`meta_id` bigint(20) NOT NULL auto_increment,
`post_id` bigint(20) NOT NULL default Ɔ',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample Data
2, 2, 'page_template', 'default'
3, 3, 'merchant_id', ?'
4, 3, 'merchant', 'SomethingSomething'
5, 3, 'link', 'http://www.website.com/link.html'
6, 3, 'thumbnail', 'http://www.website.com/thumbnail.jpg'

More Details
When the UPDATE query begins to run, the sas_postmeta table will have 500 records in it and the wp_postmeta table will have about 250,000 records in it.

Optimizing Multiple UPDATEs
Basically what I want to do comes down to this:

UPDATE players
SET ranking = 1
WHERE id = 45;

UPDATE players
SET ranking = 2
WHERE id = 30;

UPDATE players
SET ranking = 3
WHERE id = 8;

UPDATE players
SET ranking = 4
WHERE id = 97;

UPDATE players
SET ranking = 5
WHERE id = 12;
I do have a php array containing each player ID with its (new) ranking. However looping through all the players to update their rankings (as shown above) does not seem like a scalable solution to me.

Optimizing A Left Join
SELECT a.x FROM a LEFT JOIN b ON a.fk = b.pk ORDER BY a.d DESC LIMIT 10;

If I were MySQL, I would perform the above query by first ordering table a by a.d, then limiting by 10, then joining with the table b. However it seems that MySQL is doing something much much more difficult, since running this query takes 2 minutes. Here is the EXPLAIN from it, but I'm not quite sure how to read it.

+--------------+------+---------------+------+---------+------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+------+---------------+------+---------+------+------+---------------------------------+
| a | ALL | NULL | NULL | NULL | NULL | 3913 | Using temporary; Using filesort |
| b | ALL | NULL | NULL | NULL | NULL | 9471 | |
+--------------+------+---------------+------+---------+------+------+---------------------------------+
Can you see from this EXPLAIN whether MySQL is doing 3913*9471 operations ? I would think that sorting the 3913 record table + doing 10 seeks on the 9471 record table should be enough. I could easily force MySQL to "do the right" thing by just emulating this join with PHP code,

Optimizing Document Database
I am creating a document database that will be used as the backend for a website. The organization itself is cross-relational in nature, the documents are added to certain "areas" and these areas can be combined as filters to give the users precisely what they are looking for. The design works great for the current active sample (about 1100 documents). However, I began testing with a larger sample (about 25,000 documents) and the query runs way too slowly.

I actually need the database to perform operations on entries for approximately 2 MILLION documents, so the current state is unacceptable. I've placed indices on all of the tables being used, but it's still not producing the kind of speed I want. I believe the problem is in my query, but very well may be in the structure.

Here are the three tables, documents, areas, and area_assignments. Documents and areas are related through the foreign key table area_assignments, and both have a one to many relationship with this table. Code:

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?

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?

Optimizing Indexes And Queries
I have a rather large table with about 5 million unique records. I am trying to make a query that lists the unique artists in the table.

there are a set of links A B C D E F etc that when they click on them displays the artists name that starts with the letter they clicked on.

I display them in a php page that displays 50 records at a time with a little index of links at the bottom where I can have them click on a page number and show the next or previous 50 records. There are approximately 178,610 unique artists in the table the table structure is as follows: Code:

Optimizing Statistic Generation
I use a select statement to gather statistics for various time periods and would like to optimize it, if possible....this is quickly typed out for general knowledge

@lengths = 1,2,3,4,5,6,7
foreach length
select count(*) from (select * from actions WHERE datediff(current_timestamp,date) <= $length ORDER BY date DESC) AS actions GROUP BY ip;

The problem is that if I make the @lenghts about 5 times longer, a real slow down is noticed. Right now, this table has about 5,000 rows and my site is very small. Basically, every page view adds a row to the table.

Since I am VERY new to mysql (this being my first site ever with no true course on sql), is this a good idea or will I mess things up? Also, by limiting to date < the length, does this speed it up a lot because the majority of the rows might not apply once it gets a lot larger? After a year, only 7 out of 365 days will apply...

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?

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?

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).

Optimizing A SELECT .. WHERE .. LIMIT Query
(using MySQL 5.0)

First, the problem: I need to select different "pages" of IDs that match a small list of categories.

Here's my table:

CREATE TABLE `testTable` (
`id` int(10) unsigned NOT NULL auto_increment,
`category` tinyint(3) unsigned NOT NULL,
`username` char(15) NOT NULL,
PRIMARY KEY (`id`),
KEY `cat` (`category`)
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
And an EXPLAIN of my query:

EXPLAIN SELECT * FROM myTestable WHERE category IN(5,8,11) LIMIT 5000,20;

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | myTestable | range | cat | cat | 1 | null | 49904 | Using where
So from that we can see it's hitting 50,000 rows to get the 20 rows that I want (it's a table with 1,000,000 rows total).

I've been using MySQL for a fair amount of time, but I haven't ever done much optimization beyond the standard "add an index on it".

Optimizing LIKE Queries : E.g. Filename LIKE '%filename_part%'
I have implemented a ftp search engine which stores the names of all files present on the FTP servers in LAN and searches for them on request.

The database has roughly 15 lakh records.For searching filenames I need to use the following select query:

select * from filenames where filename like '%filename_part%' ;

This query takes around 10-15 seconds to be executed. Normal indexing seemed useless for this query.So I have tried to use full-text indexing.I retrieved results within fraction of second but it does not retrieve all possible results and retrives only partial results.I am using mysql-4.0.15 . Is it by any chance defect of this version and has been implemented better in higher versions.The match query I used was:

select * from filenames where match(filename) against('+filename_part%' in boolean mode);

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:

Optimizing Queries Of Larger Databases
I'm trying to find out if there is a "better" way to do what I'm trying to do here.
I have a table with about 2 million entries in it. What i need to do is find entries with a given ID and return the result for a few of the fields. The way i'm doing it is simply:

select field1, field2 from table where id = 2000000;

This takes about a second to execute, which sounds great, except that the script i[m using it in could need to execute a few hundred of these queries within a time limit of about 5 minutes.

What i'm immagining the problem is, is that MySQL is starting at the first entry, and looking for the ID i've requested. Because there are so many entries it takes a while to get to the entry i've told it to return.

Can i make it only search within say.. the last 1000 entries? Or search starting at the end of the table?

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.

Optimizing A Deletion By Date Field?
i have a field that is described as follows:

Field | Type | Null | Key | Default | Extra
------------------------------------------------------------------
Dt_From | date | YES | MUL | NULL |

I am using JDBC to do some maintenance on this DB. The first thing I need to do is
delete all rows where the 'From' date is before a specified date. I am getting, what I believe to be, horrible results in terms of speed. This is an indexed field, as
shown above. Here is the query string I am using:

"DELETE from allrows where Dt_From <= 20050404;"

My test DB here has just shy of 10 million rows. This query should delete around 1.8 million of those.

I launched my test almost 2 hours ago and it still hasn't finished. Is that to be expected? I would think that with an indexed field I should get much quicker results. Am I wrong here? Is there something I'm missing?

Optimizing Search Count & Order By
Basic searches run for about a second or so, and searches within a range of dates runs for about 3 seconds on average. Neither is great but it suffices for now.

Unfortunately, to get it in that time, we always have to limit our result set to only what is needed for the page (~20), so we can never get an actual count on the full result set. Trying a simple COUNT(*) on the search query before pulling only the 20 needed takes forever. Also, any Order By on the date takes forever.

Our search table structure is below:
articleID int(11)
id varchar(25)
issuedate date
subhead varchar(150)
title varchar(254)
abstract text
titlename varchar(100)
alphadate varchar(60)
pageno varchar(10)
summary text
countId bigint(21)
category set('a', 'p', 'b')
three_in_one tinyint(1)

I just started working for this company in June, and I thought of converting the issuedate from YYYY-MM-DD to a unix timestamp, removing alphadate and possibly id & summary, and normalizing titlename (which is the article's publication), but I wasn't sure if this would help any of the search queries. The table is at 1.33 gb with a full-text index taking 350 kb of that.

Optimizing Table - Count Taking Too Long
Could anyone help me with some advise on optimizing a table? It currently has 600,000 records and 'select count(*) from products where 1' is taking several seconds to return a result.

Mysql is running locally on my development machine (XP) as a service and is version 5.0.24a

The table is defined as follows:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`description` text,
`taxable` tinyint(1) default Ɔ',
`sub_title` varchar(255) default NULL,
`isbn` varchar(13) default NULL,
`format` varchar(40) default NULL,
`published_date` datetime default NULL,
`pages` int(11) default NULL,
`refundable` tinyint(1) default NULL,
`refund_by_date` datetime default NULL,
`publisher` varchar(255) default NULL,
`bic_classification` varchar(10) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_products_on_isbn` (`isbn`),
KEY `index_products_on_name` (`name`),
KEY `index_products_on_bic_classification` (`bic_classification`),
KEY `index_products_on_publisher` (`publisher`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Optimizing Slow Queries On Millions Of Records
I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.

When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
Now it takes about 38 seconds for all queries in the example below.
MySQL 4.1 on Windows

I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time. Code:

Analyze Table - Optimizing Time-consuming Queries
I'm starting a website-counter service, so basically my site requires a lot of mysql activity.

Here are the two tables I use:

CREATE TABLE `traffictrack` (
`id` int(11) NOT NULL auto_increment,
`nr` int(11) NOT NULL default Ɔ',
`ip` int(10) unsigned NOT NULL default Ɔ',
`ndate` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `ndate` (`ndate`)
)

CREATE TABLE `tracker` (
`nr` int(11) NOT NULL auto_increment,
`email` varchar(70) NOT NULL default '',
`password` varchar(220) NOT NULL default '',
`mdate` datetime NOT NULL default &#55612;&#57200;-00-00 00:00:00',
`ip` int(10) unsigned NOT NULL default Ɔ',
`image` varchar(200) NOT NULL default '',
`friendid` int(11) NOT NULL default Ɔ',
KEY `mdate` (`mdate`),
KEY `nr` (`nr`)
)
I have some queries that take up more than 1 second:

PHP

SELECT INET_NTOA(traffictrack.ip) as ip, tracker.image, UNIX_TIMESTAMP(traffictrack.ndate) as ndate, tracker.friendid, ( UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(traffictrack.ndate) ) as dif
FROM traffictrack
LEFT JOIN tracker USING(ip)
WHERE traffictrack.nr = ?'
AND
DATE_FORMAT(traffictrack.ndate, '%Y-%m-%d')=&#55614;&#57158;-07-23'
ORDER BY traffictrack.ndate DESC
LIMIT 20

also:

PHP

SELECT COUNT( traffictrack.ndate ) AS total, tracker.image, tracker.friendid, UNIX_TIMESTAMP(traffictrack.ndate) as timest
FROM traffictrack
LEFT JOIN tracker
USING ( ip )
WHERE traffictrack.nr = ?'
AND (
UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( traffictrack.ndate )
) < 600
GROUP BY tracker.nr
LIMIT 0 , 30

I believe it's because my tables are badly indexed, I added an EXPLAIN before these two queries, they both ended up with:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE traffictrack ALL NULL NULL NULL NULL 34246 Using where; Using temporary; Using filesort
1 SIMPLE tracker ALL NULL NULL NULL NULL 20972
If someone knows how to speed up my queries, please help.