MYSQL :: Optimizing Slow Queries On Millions Of Records

Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB.
According our calculations we were never to exceed 500,000 records in
the DB. Seems we were off by a decimal point or so.

I set up a FreeBSD box with MySQL three years back and it has been
filling up. One table has over 4,000,000 records. Yes, four million.

As a web developer and not a DBA, I have struggled to upkeep the server
the best I can. As of the last one million records the server has been
struggling to keep up with multiple requests and as you can imagine the
user base is growing too.

Hardware:
Dual Xeon 3.06 Ghz
4 GB ECC RAM
800GB RAID5 SATA array

Software:
FreeBSD 5.3
Apache 2.0
PHP5
MySQL 5.0.2

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

The table is MyISAM with a single primary key that is used largely for
updating row data.

Most records are ten digit numbers for one column and a ten digit
varchar for the other, but sometimes either column can be a series of
characters up to 100 chars long, so each column is set for
varchar(100).

The action performed is

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

then...

SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25

with "%123%" being any random string typed into a search window.

View Replies !

How To Optimize Millions Of Records?
i am having aprrox 35 lac records in a table ...

if i delete 5 lac records who have state='deleted' then how can i optimize this table while we are maintaining two different servers, one of them is master and one is slave where master is replicating the records on slave.

keeping that in consideration that both are working servers ...

View Replies !

Mediumtext Fields And Millions Of Records?
Could I have some advice please. I am creating a table that needs to hold around 90 million records. I will have a indexed integer field for lookups and a mediumtext field which will hold on average data lengths of 18kb and the potential for some data lengths to reach 500kb, but 500kb ones will be rare.

The table holings the 90 million records will grow on average around 200 records per day via a insert statement.

My site will have a number of users who will be looking up information from the 90 million record table using the indexed field.

The table will be performing lookups more that it will inserts, so I have a couple of questions:

Performance: Should I have any worries with the above? Is there any problems associated with holding 90 million records of 18kb record lengths that I should be aware of. I have created 1 million records as a test and a typical select takes 73ms so all looks ok.

Table Type: For the above, should I be sticking with MySiam or InnoDB? The main purpose of this table will be user lookups (Select Statements) with a limited amount of inserts per day.

Maintenance: Perdiodically I will want to remove records older than X days old. This potentially may be my biggest performance hit? Any advice in this area would be great.

View Replies !

Would MySQL Handle Millions Of Records Tables
I already found on the MySQL web site that some users did have good
results with tables of some millions records.

But, what I want to do is store some hundreds millions records in a
table. 190 millions to start and maybe a lot more after. Does someone
already use MySQL with such a quantity of data ?

I was also asking myself how a simple SELECt query like this one below
would perform on such a table.

SELECT * FROM MyTable WHERE MyPrimaryKeyField = Value;

I think that with indexes this shoudl be fast despite the fact that
the table is huge.

An other kind of query i'd like to do is :

SELECT Count(*) FROM MyTable WHERE ABooleanField = True;

I expect this one to take several minutes, maybe hours. Is my guess
right or could it be lot faster/slower ?

View Replies !

Huge Table To Store Tens Of Millions Of Records
I want to create a table to store tens of millions of records. Is my create table statement suitable for the job?

CREATE TABLE `table_format` (
`id` int(11) default NULL,
`name` varchar(100) default NULL,
`addresses` char(200) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 AVG_ROW_LENGTH=200 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED

is fixed row format making average row format parameter obsolete?

View Replies !

Slow Queries When Using Views Compare To Direct Table Queries
Iâ€™ve having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that Iâ€™m seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one:

MySQL Code: ...

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 !

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:

View Replies !

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?

View Replies !

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

View Replies !

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.

View Replies !

Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.

View Replies !

Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form.
My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot?
Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?

View Replies !

What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?

View Replies !

How To Log-slow-queries
I'm running MySQL 5.0.

Now I would like to log-slow-queries. With MySQL 4 I used

mysqld --log-slow-queries --log-long-format start

but now I get following note:

mysqld: Too many arguments (first extra is 'start').
Use --help to get a list of available options

It looks like starting and stopping now only works with

etc/init.d/mysql start

View Replies !

Slow Queries And Optimization
I am hosting a site with an increasing number of members. Letely the site has become slower because of too many queries are being used.
Is there a way of asking the server which queries are used most often, as well as asking which queries take a long time to process so that I would know where to start my optimization process?

View Replies !

Slow View Queries
I have a fairly complex view which, when you execute a SELECT * shows all info very quickly (0.24 seconds to return nearly 3000 rows). However, when I start throwing slightly more complex queries at it, things really slow down - an average query can take 40 seconds to execute.

View Replies !

Enabling -- Log - Slow - Queries
I want to enable --log-slow-queries of MySQL.

How do I do it without restarting the DB server?

View Replies !

Slow Queries With LIMIT
Working with a large table (1 Million Records). Setting up pagination using LIMIT command.

Queries near the end of the table are slow when non-indexed fields are selected (but not included in the 'where' clause).

For example:

With indexes on id (PK), and type:

SELECT id, user_id, name, type, selected, creation_date FROM titans where type = 2 LIMIT 500010,10;
+---------+---------+------------+------+----------+---------------+
| id | user_id | name | type | selected | creation_date |
+---------+---------+------------+------+----------+---------------+
| 1000016 | 100001 | Uni Ekko | 2 | 0 | 1228149051 |
| 1000017 | 100001 | Dynaneedle | 2 | 0 | 1228149074 |
+---------+---------+------------+------+----------+---------------+
2 rows in set (4.17 sec)

If I only search on the ID field, the query is much faster.

mysql> SELECT id FROM titans where type = 2 LIMIT 500010,10; +---------+
| id |
+---------+
| 1000016 |
| 1000017 |
+---------+
2 rows in set (0.36 sec)

explain SELECT id, user_id, name, type, selected, creation_date FROM titans where type = 2 LIMIT 500010,10;
+----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+
| 1 | SIMPLE | titans | ref | avatars_indx2,tst | avatars_indx2 | 2 | const | 381988 | Using where |
+----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+
1 row in set (0.01 sec)

View Replies !

Do Unnecessary Parentheses Slow Queries Down?
I know a person who seriously writes queries like this:

SELECT * FROM my_table WHERE ((((my_col = 30))) AND (((other_col = 40))))

I told him all those parenthesis were unnecessary and he responded that he does it so later if he has to add more criteria the parenthesis will be there. I about fell out of my chair that sounds so ridiculous. So my question is, other than the obvious ugliness and being hard to read, is there any other reasons why NOT to do this?

View Replies !

Fine Tune :: Timeout Slow Queries?
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours.

My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries)

View Replies !

MySQL Timing Out? Really Slow Queries, Already Indexed.
For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got:

Uptime: 220372
Threads: 39
Questions: 18748899
Slow queries: 808
Opens: 28723
Flush tables: 1
Open tables: 4096
Queries per second avg: 85.078

The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling.

Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096:
query_cache_min_res_unit = 4096
table_cache = 4096
transaction_prealloc_size = 4096

Do any of those have anything to do with this? Am I even close? Are any of those values from mysql_stat a big deal?

View Replies !

Table Gets Slow At Around 60k Records
I've a table that starts getting slow with count(val) and select ... order by when it has around 60k records. Table type is innodb

Strange enough, at 50k records the response time is great, but looks like it grows exponentially after that.
Its something like
50k records - 0.2 seconds for count(val)
60k records - 3 seconds
100k records - 20 seconds

I realize order by and count forces mysql to go through the entire table...

View Replies !

815,000+ Records, DB Table Query Too Slow
I have a database table with about 815,000 records in it, each record containing the source of individual webpages (with all the tags and multiple spacing stripped out leaving just the words). At first my match boolean queries were fine but once I got to around 100,000+ records it became too slow to use for a web-based search tool. I was able to separate the data into 10 tables and then use AJAX to run the match query on all 10 tables at the same time and that has increased performance.

My question is, is it possible for me to increase the performance for the 1 table some how instead of resorting to separating the data into multiple tables? Is MySQL the right DB for this kind of setup, would MSSQL or PostgreSQL perform better for larger sets of data? I'm not sure how MySQL 5 clustering works,

View Replies !

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

View Replies !

1 Table For Millions Of Rows
I have one table which may record possibly millions of hits and i want to know if this is a good idea?

View Replies !

How To Work With Millions Of Rows
I need to handle few millions rows(cca 25M) with about 100 bytes for each row. They will be indexed (4 byte integer) becouse I need fast data retrieval.

Would it be better to store it in multiple tables or all in one table?

What type of table engine should I use and how fast should I expect retrievals to be(with using indexed colum)? About hardware it isn't anything great or slow, just normal desktop PC.

View Replies !

Mysqldump With Millions Of Rows (ibdata1 File Over 11GB)
New user successfully created database with millions of rows (ibdata1 file over 11GB). Backup takes for ever using mysqldump. Am I running utility wrong or is there another utility that runs in less than 18 hours....?

View Replies !

How To Optimize Count(*) Operation In A Large (millions Rows) InnoDb Table?
Table A:
varchar(255) userId;
bigint itemI
bigint itemJ

for query like
select count(*) from table A where A.itemId = XXX

View Replies !

Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc.

I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables.

My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page.

Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?

View Replies !

Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this:

Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined.

The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.

View Replies !

Long Queries VS Multiple Short Queries
I have a php script that requests a very long query from our mysql database. It has lots of joins and accesses at least 5-6 tables. My question is should i break it up into smaller separate queries or leave it as one long one, in regards to best practices?

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

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 !

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 !

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 !

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 !

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;

View Replies !