Tag Info

New answers tagged

2

Please keep in mind OPTIMIZE TABLE is DDL not DML. In terms of mechanism, this is what OPTIMIZE TABLE mydb.mytable; does under the hood ALTER TABLE mydb.mytable ENGINE=MyISAM; ANALYZE TABLE mydb.mytable; or CREATE TABLE mydb.mytable_new LIKE mydb.mytable; ALTER TABLE mydb.mytable_new DISABLE KEYS; INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable; ...


2

I took the liberty of editing your SQL so the clauses are on separate lines. I violated CodeReview group discipline by doing that. Sorry! My suggestion to you is to format your queries so the various clauses are clearly visible, as I did. This kind of formatting, in my experience, makes it easier to understand the logic of queries and to spot errors. ...


0

I've just figured out how to do this, so I'll answer it in case anyone else has a similar problem. Introduced SELECTs as far down the tree as possible, to reduce the number of comparisons needed. Introduced PROJECTs as far down the tree too, to reduce the number of columns. Used a JOIN wherever a CARTESIAN PRODUCT was followed by a SELECT. (There weren't ...


4

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control. The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential. Only after an index has more than eight pages will it be allocated additional pages from uniform extents. At fewer than 1,000 rows, your clustered index ...


2

You want to use a TRANSLATIONS table, that looks something like: CREATE TABLE TRANSLATIONS ( translation_id INTEGER, language_id INTEGER, translation VARCHAR(255), PRIMARY KEY (translation_id, language_id) ); Then edit your current schema and/or code to reference the TRANSLATIONS table, rather than any hard-coded values (using ...


0

The answer relies on what kind of data you store and how. I would never suggest making separate database. Depending on the data you can either: Create separate rows in same tables and add "language" column to specify which language the content is in. Create separate table per langauge to completely separate content. Which way to go depends on your data, ...


0

If it was working recently and it's not working now, you've either got a whole bucket load more data or a worse query plan. I'd suggest you'd notice the former. The latter may be due to the statistics getting stale.


0

The problem is this: ROUND( 6371 * ACOS(COS(RADIANS(50.58907000)) * COS(RADIANS(v0_.lat)) * COS(RADIANS(v0_.lng) - RADIANS(3.16710500)) + SIN(RADIANS(50.58907000)) * SIN(RADIANS(v0_.lat))), 2 ) AS sclr5 If this calculation is always the same you can add an extra DB field (sclr5) that holds this information. You may update the ...


4

I see a couple of issues. The biggest one is that PG is using a sequence scan on A when filtering A. I think you need a composite index on A.flag AND A.strvalue. If there is already an index available, PostgreSQL is choosing not to use it for some reason. This seems to be eating up 92% of your cost estimate and is likely what's making it run for so long. ...


0

The first methods slower than the second one most like is because your new table, OrderInformation, does not contain a lookup index for the Order ID column. I believe that if you add an index both of these methods will execute with similar execution times


1

You just need to add a B-tree index to the some_date DB field. Partial indexes work, only if you know your query parameters and want to exclude / include particular ranges (http://www.postgresql.org/docs/current/static/indexes-partial.html). Another alternative is to use table partitioning ...


2

According to the manual, the query cache removes the Least Recently Used (LRU) Query from the Query Cache when the Query Cache is full. Read the general chapter on the MySQL Query Cache for a more detailed description of the logic and architecture of the Query Cache.


3

The query to accomplish this would be of the form: SELECT t.UniqueKey FROM mytable t WHERE t.UniqueKey = ? AND t.timeStamp >= NOW() - INTERVAL 5 MINUTE AND t.timeStamp <= NOW() LIMIT 1 (This query assumes that the timeStamp column is defined as datatype TIMESTAMP.) The query will either return one or zero rows, which will indicate either ...


1

For the table mydb.mytable with UniqueKey and timeStamp, to see if the UniqueKey exists within the last 5 minutes, simply run this SELECT COUNT(1) FROM mydb.mytable WHERE UniqueKey = ???? AND timeStamp >= ( NOW() - INTERVAL 5 MINUTE ); or SELECT COUNT(1) FROM mydb.mytable WHERE UniqueKey = ???? AND timeStamp >= ( NOW() - INTERVAL 300 SECOND ); ...



Top 50 recent answers are included