Table optimization ideas needed
in [Databases]
|
From: Shelley on 24 Mar 2008 23:30 Hi all, I made a post a week ago to ask for the idea of the fastest way to get table records. Fyi, http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table Look at the time even a 'count(1)' took. Then you can imagine how much time sql such as "select a,b from table_name where c='d'" will take. I have a lot of tables like that. So my questions is: What's your practice to optimize tables like that? Thanks in advance. -- Regards, Shelley
From: Chris on 24 Mar 2008 23:50 Shelley wrote: > Hi all, > > I made a post a week ago to ask for the idea of the fastest way to get > table records. > Fyi, > http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table > > > Look at the time even a 'count(1)' took. > Then you can imagine how much time sql such as "select a,b from > table_name where c='d'" will take. > > I have a lot of tables like that. So my questions is: > What's your practice to optimize tables like that? I pretty much follow what I've said in this article: http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database -- Postgresql & php tutorials http://www.designmagick.com/
From: "J. Hill" on 25 Mar 2008 01:18 From a quick perusal of the article Chris mentions, I'd generally agree with that view about table optimization -- I'm not an expert on Postgres, but the recommendations generally seem to apply to MySQL as well. My basic view is that, if you are routinely doing a select on millions of rows, you probably need to take a step back and consider your general structure. Without revising the structure and other than indexing as Chris suggested, a couple off-the-cuff ideas: if the stability of the table is not critical, use MyISAM tables rather than InnoDB tables; try using stored procedures (MySQL>=5.0). While it isn't always true, my experience is that any table with a million rows or more is a problem created because the initial assumption was that the table would never grow that large so the general data structure was not fully thought through. Google is capable of handling searches through billions of rows of data not because it uses supercomputers but because of its data structure. Just my two centavos, Jeff Chris wrote: > Shelley wrote: >> Hi all, >> >> I made a post a week ago to ask for the idea of the fastest way to >> get table records. >> Fyi, >> http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table >> >> >> Look at the time even a 'count(1)' took. >> Then you can imagine how much time sql such as "select a,b from >> table_name where c='d'" will take. >> >> I have a lot of tables like that. So my questions is: >> What's your practice to optimize tables like that? > > I pretty much follow what I've said in this article: > > http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database >
From: Shelley on 25 Mar 2008 22:16 Yes, Index can help a lot. But actually there has been five indices. The table takes 1.4G space while the indices take 2.3G. The select sentence is still slow. :( On Tue, Mar 25, 2008 at 11:50 AM, Chris <dmagick(a)gmail.com> wrote: > Shelley wrote: > > Hi all, > > > > I made a post a week ago to ask for the idea of the fastest way to get > > table records. > > Fyi, > > > http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table > > > > > > Look at the time even a 'count(1)' took. > > Then you can imagine how much time sql such as "select a,b from > > table_name where c='d'" will take. > > > > I have a lot of tables like that. So my questions is: > > What's your practice to optimize tables like that? > > I pretty much follow what I've said in this article: > > http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > -- Regards, Shelley
From: Chris on 25 Mar 2008 22:20 Shelley wrote: > Yes, Index can help a lot. > But actually there has been five indices. The table takes 1.4G space > while the indices take 2.3G. > The select sentence is still slow. :( Post your exact query, table definition(s), indexes and see if anyone has some suggestions. If it's a mysql db, join one of the mysql lists and see if anyone has some suggestions (though they will ask for the same info). Same for any other db. -- Postgresql & php tutorials http://www.designmagick.com/
|
Next
|
Last
Pages: 1 2 3 4 Prev: Table optimization ideas needed Next: HTML & CSS (Off Subject) 911 Help! :-) |